declare @t table(id int,code varchar(8)) insert into @t select 1 ,'A' insert into @t select 2 ,'B' insert into @t select 3 ,'C' insert into @t select 4 ,'D' insert into @t select 5 ,'D' insert into @t select 6 ,'A' insert into @t select 7 ,'B' insert into @t select 8 ,'D' insert into @t select 9 ,'C' insert into @t select 10,'A'select C1=max(case t.code when 'A' then t.code else '' end), C2=max(case t.code when 'B' then t.code else '' end), C3=max(case t.code when 'C' then t.code else '' end), C4=max(case t.code when 'D' then t.code else '' end) from (select a.*,(select count(1) from @t where id<=a.id and code=a.code) as num from @t a) t group by t.num/* C1 C2 C3 C4 -------- -------- -------- -------- A B C D A B C D A D */
CREATE TABLE TBTEST(ID INT,STRING VARCHAR(10)) INSERT TBTEST SELECT 1, 'A' UNION ALL SELECT 2, 'B' UNION ALL SELECT 3, 'C' UNION ALL SELECT 4, 'D' UNION ALL SELECT 5, 'D' UNION ALL SELECT 6, 'A' UNION ALL SELECT 7, 'B' UNION ALL SELECT 8, 'D' UNION ALL SELECT 9, 'C' UNION ALL SELECT 10,'A'SELECT ID=IDENTITY(INT ,1,1),STRING INTO #T FROM TBTEST WHERE STRING='A'SELECT ID=IDENTITY(INT ,1,1),STRING INTO #T1 FROM TBTEST WHERE STRING='B' SELECT ID=IDENTITY(INT ,1,1),STRING INTO #T2 FROM TBTEST WHERE STRING='C' SELECT ID=IDENTITY(INT ,1,1),STRING INTO #T3 FROM TBTEST WHERE STRING='D'SELECT T.STRING,T1.STRING,T2.STRING,T3.STRING FROM #T T FULL JOIN #T1 T1 ON T.ID=T1.ID FULL JOIN #T2 T2 ON T.ID=T2.ID FULL JOIN #T3 T3 ON T.ID=T3.ID STRING STRING STRING STRING ---------- ---------- ---------- ---------- A B C D A B C D A NULL NULL D(所影响的行数为 3 行)
insert into @t select 1 ,'A'
insert into @t select 2 ,'B'
insert into @t select 3 ,'C'
insert into @t select 4 ,'D'
insert into @t select 5 ,'D'
insert into @t select 6 ,'A'
insert into @t select 7 ,'B'
insert into @t select 8 ,'D'
insert into @t select 9 ,'C'
insert into @t select 10,'A'select
C1=max(case t.code when 'A' then t.code else '' end),
C2=max(case t.code when 'B' then t.code else '' end),
C3=max(case t.code when 'C' then t.code else '' end),
C4=max(case t.code when 'D' then t.code else '' end)
from
(select a.*,(select count(1) from @t where id<=a.id and code=a.code) as num from @t a) t
group by
t.num/*
C1 C2 C3 C4
-------- -------- -------- --------
A B C D
A B C D
A D
*/
INSERT TBTEST
SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 3, 'C' UNION ALL
SELECT 4, 'D' UNION ALL
SELECT 5, 'D' UNION ALL
SELECT 6, 'A' UNION ALL
SELECT 7, 'B' UNION ALL
SELECT 8, 'D' UNION ALL
SELECT 9, 'C' UNION ALL
SELECT 10,'A'SELECT ID=IDENTITY(INT ,1,1),STRING INTO #T FROM TBTEST WHERE STRING='A'SELECT ID=IDENTITY(INT ,1,1),STRING INTO #T1 FROM TBTEST WHERE STRING='B'
SELECT ID=IDENTITY(INT ,1,1),STRING INTO #T2 FROM TBTEST WHERE STRING='C'
SELECT ID=IDENTITY(INT ,1,1),STRING INTO #T3 FROM TBTEST WHERE STRING='D'SELECT T.STRING,T1.STRING,T2.STRING,T3.STRING
FROM
#T T
FULL JOIN #T1 T1 ON T.ID=T1.ID
FULL JOIN #T2 T2 ON T.ID=T2.ID
FULL JOIN #T3 T3 ON T.ID=T3.ID
STRING STRING STRING STRING
---------- ---------- ---------- ----------
A B C D
A B C D
A NULL NULL D(所影响的行数为 3 行)