1. Contain two tables
- Establish test environment CREATE TABLE TB1 (ID nvarchar (10), Type Nvarchar (10)) Insert Into TB1 Select '11', 'A' Union All Select '22', 'B' Union All Select '33', ' C '
Create Table TB2 (N INT, TYPE NVARCHAR (10), NUM INT) INSERT INTO TB2 SELECT '1', '11', '4' Union All Select '1', '11', '5' Union All Select '2 ', '22', '8' Union All SELECT '3', '22', '5'
- Query processing declare @SQL varchar (8000) set @ SQL = 'select n' select @ SQL = @SQL ', SUM (Case when type =' TTT 'TEN NUM ELSE 0 End) [' TT ']' From (SELECT DISTINCT A.TYPE AS TT, ISNULL (B. Type, '0') AS TTT from TB2 B Right Join TB1 A on A.ID = B.TYPE) B
Set @ SQL = @ SQL 'from TB2 Group by N'Print @SQLEXEC (@SQL) GO - Delete Test Environment DROP TABLE TB1, TB2
2.
- Establish test environment Create Table TB2 (ID INT, TYPE NVARCHAR (10)) Insert INTO TB2 SELECT 1, 'A'Insert Into TB2 Select 1,' B'Insert Into TB2 SELECT 1, 'C'insert Into TB2 SELECT 1 , 'd'INSERT INTO TB2 SELECT 2,' A'Insert Into TB2 SELECT 3, 'B'Insert INTO TB2 SELECT 4,' C'insert INTO TB2 SELECT 4, 'D'GO - Query Process Create Function F_catstring (@ ID int) returns nvarchar (1000) as begin declare @S nvarchar (1000) set @ s = '' select @ s = @ s ',' type from tb2 where id = @ id return (stuff (@ s, 1, 1, '')) Endgo - Call Function Select ID, DBO.F_CATSTRING (ID) AS TYPE from TB2 Group By IDGO
- Delete the test environment
DROP FUNCTION F_CATSTRINGDROP TABLE TB2
3. Another situation
- Example data
Create Table TB (ID INT, Name Varchar (8000)) Insert TB SELECT 1, ', 1, 3, 4,' Union All Select 2, ', 12, 34, 67, 89,' Go - Conversion Processing SELECT TOP 8000 id = Identity (int) Into # from syscolumns a, syscolumns bselect a.id, name = substring (a.Name, B.ID, Charindex (',', a.name ',', b.id) -b .id) from TB A, # bwhere len (a.name)> B.ID and substring (',' a.Name, B.ID, 1) = ',' and substruct (A.Name, B.ID) 1) <> ',' Order by A.ID, B. IDDROP TABLE #GO
- Delete DROP TABLE TB
/*--result
ID Name ----------------------- 1 11 31 42 122 342 672 89
(The number of rows affects is 7 lines) - * /
Declare @a table (id int, n nvarchar (100)) insert INTO @A SELECT 1, ', 1, 3, 4,' INSERT INTO @A SELECT 2, ', 12, 34, 67, 89,'
Declare @s nvarchar (4000) set @S = '' select @ s = @ s replace (n, len (n) -1), ',', 'union all select' quotename (id, '' '') ',') from @aset @ s = stuff (@ s, 1 ,11, ') print @sexec (@S)
4. Establish a test environment create table test (id int, out of the library 1 int, out of the library 2 int, warehouse 1 int) - Insert Data INSERT TEST VALUES (1, 20, 50, 100) Insert Test VALUES (2 , 30, 30) Insert Test Values (4, 50, 10, 100) GO - Test Declare @SQL NVARCHAR (4000) SET @ SQL = N'SELECT DISTINCT 'SELECT @ SQL = @ SQL RTRIM (ID) 'AS ID' RTRIM (ID) N ', (Select Sum (-Inull 1,0) -Innull (Out of Library 2,0) Isnull (Gear 1,0)) from test where id = ' RTRIM (ID) N') AS Stock ' RTRIM (ID) ', 'from testselect @ SQL = Left (@ SQL, LEN (@SQL) -1) ' from test'exec (@SQL ) GO - Remove Test Environment DROP TABLE TESTGO - Result / * ID1 Stock 1 ID2 Stock 2 ID3 Stock 31 30 2 0 4 40 * / - Example Data Create Table TB (Number Int) Insert Tb Select 1Union All Select 2Union All SELECT 3UNION All Select 4UNION All Select 6Union All Select 8UNION All SELECT 10UNION All SELECT 11UNION All SELECT 12GO
- Processing Select ID = Identity (int), number INTO #A from Tb Awhere Not Exists (SELECT * from TB Where Number = a. Number -1) SelectiT Id = Identity (int), number INTO #B from Tb Awhere Not EXISTS (SELECT * from Tb Where Number = a. Number 1) SELECT number = Cast (a. number as varchar) Case A. Number when b. Number Then '' Else '~' Cast (b. number AS VARCHAR) End, quantity = b. Number - A. Number 1From # aa, # b bwhere a.id = B.IDDROP TABLE # a, # bgo
- Delete DROP TABLE TB
/ * - Result section quantity ---------------- 1 ~ 4 46 18 110 ~ 12 3
(The number of rows affects is 4 lines) - * /
Create Table Studentmark (ID INT, StudentId Int, Kind Varchar (16), Mark Float, Testtime DateTime, Testk IndidId Int) Insert Into StudentMarkSelect 1, 20, 'Language', 90, '2004-05-06', 1 Union AllSelect 2 , 20, 'Mathematics', 80, '2004-05-06', 1 Union Allselect 3, 20, 'English', 70, '2004-05-06', 1 Union AllSelect 4, 21, 'Language', 60 , '2004-05-06', 1 Union Allselect 5, 21, 'Mathematics', 70, '2004-05-06', 1 Union Allselect 6, 21, 'English', 90, '2004-05-06' , 1 Union AllSelect 7, 23, 'Language', 50, '2004-05-06', 1 Union AllSelect 8, 23, 'Mathematics', 40, '2004-05-06', 1 Union AllSelect 9, 23, 'English', 20, '2004-05-06', 1 Union AllSelect 10, 20, 'Language', 90, '2004-02-26' , 2 Union AllSelect 11, 20, 'Mathematics', 80, '2004-02-26', 2 Union Allselect 12, 20, 'English', 70, '2004-02-26', 2 Union Allselect 13, 21, 'Language', 60, '2004-02-26', 2 Union Allselect 14, 21, 'Mathematics', 70,' 2004-02-26 ', 2 Union Allselect 15, 21,' English ', 90,' 2004 -02-26 ', 2 Union AllSelect 16, 23,' Language '
, 50, '2004-02-26', 2 Union Allselect 17, 23, 'Mathematics', 40,' 2004-02-26 ', 2 Union Allselect 18, 23,' English ', 20,' 2004-02- 26 ', 2Declare @SQL VARCHAR (8000) Set @ SQL =' Select Distinct StudENTID 'SELECT @ SQL = @SQL ', '' ' Kind ' '' AS ' Kind ', (Select Mark from Studentmark Where Kind = ' '' Kind '' 'and testkindid = a.testkindidid and studentid = a.studentid) AS Mark' KindFrom (Select Distinct Kind from Studentmark)
Set @ SQL = @ SQL ', Testkindid from Studentmark A'Print @sqlexec (@SQL) Drop Table StudentMark
Create Table TB (ID INT, things nvarchar (20), result nvarchar (20)) Insert Into TB SELECT 1, 'Eating', 'Eat' Insert Into TB SELECT 2, 'Eat', 'INSERT INTO TB Select 1, 'Go to work', 'on time to' Goalter Table Tb Add Sid IdenTies (1, 1) GO
SELECT ID, things, result, sid = (select count (*) from tb where id = a.id and sid <= a.sid) INTO #T from TB A
--Select * from #t
Declare @SQL VARCHAR (8000) SET @ SQL = 'Select Distinct ID' SELECT @ SQL = @SQL ', (SID AS NVARCH (10)) ' ' And id = a.id) AS things' CAST (SID as nvarchar (10)) ', (Select Result from #t where sid =' ' Cast (SID AS NVARCHAR (10)) ' '' AND ID = A.ID) AS Result ' Cast (SID AS NVARCHAR (10)) from (SELECT DISTINCT SID FROM #T) B
Set @ SQL = @ SQL 'from #t a'print @sqlexec (@SQL)
Drop Table TB, # T
