关于行列转换用pivot的有关问题

关于行列转换用pivot的问题
如何实现以下数据的行列转换：

SQL code

--数据id   type 1    23 1    12 1    103 2    45 2    98 4    1 4    104 4    458 4    123 4    90  --行列转换后，type最多5列， id  type1 type2 type3 type4 type5  1  23    12    103   null  null  2  45    98    null  null  null  4  1     104   458   123   90

SQL code

select id ,        max(case px when 1 then type else null end) type1,       max(case px when 2 then type else null end) type2,       max(case px when 3 then type else null end) type3,       max(case px when 4 then type else null end) type4,       max(case px when 5 then type else null end) type5from(  select t.* , px = (select count(1) from tb where id = t.id and type < t.type) + 1 from tb t) mgroup by id
[解决办法]
SQL code
use Tempdbgo--> -->  if not object_id(N'Tempdb..#T') is null    drop table #TGoCreate table #T([id] int,[type] int)Insert #Tselect 1,23 union allselect 1,12 union allselect 1,103 union allselect 2,45 union allselect 2,98 union allselect 4,1 union allselect 4,104 union allselect 4,458 union allselect 4,123 union allselect 4,90GoDECLARE @s NVARCHAR(4000),@i NVARCHAR(3)Select TOP 1 @i=COUNT(*),@s='' from #T GROUP BY ID order by count(*) descWHILE @i>0    SELECT @s=N',[type'+@i+']=max(case when Row='+@i+N' then [type] end)'+@s,@i=@i-1EXEC(N'SELECT ID'+@s+N'FROM (select *, row=row_number()over(partition by ID order by ID)from #T) as aGROUP BY ID')go/*ID    type1    type2    type3    type4    type51    23    12    103    NULL    NULL2    45    98    NULL    NULL    NULL4    1    104    458    123    90*/
[解决办法]
SQL code
use Tempdbgo--> -->  if not object_id(N'Tempdb..#T') is null    drop table #TGoCreate table #T([id] int,[type] int)Insert #Tselect 1,23 union allselect 1,12 union allselect 1,103 union allselect 2,45 union allselect 2,98 union allselect 4,1 union allselect 4,104 union allselect 4,458 union allselect 4,123 union allselect 4,90GoDECLARE @s NVARCHAR(4000),@i NVARCHAR(3)Select TOP 1 @i=COUNT(*),@s='' from #T GROUP BY ID order by count(*) descWHILE @i>0    SELECT @s=N',[type'+@i+']'+@s,@i=@i-1SET @s=STUFF(@s,1,1,'')EXEC(N'SELECT ID'+@s+N'FROM (select *, row=''type''+rtrim(row_number()over(partition by ID order by ID)) from #T) as apivot(max([type]) for row in('+@s+') )as b')go/*type1    type2    type3    type4    type51    12    103    NULL    NULL2    98    NULL    NULL    NULL4    104    458    123    90*/
[解决办法]
改改
SQL code
use Tempdbgo--> -->  if not object_id(N'Tempdb..#T') is null    drop table #TGoCreate table #T([id] int,[type] int)Insert #Tselect 1,23 union allselect 1,12 union allselect 1,103 union allselect 2,45 union allselect 2,98 union allselect 4,1 union allselect 4,104 union allselect 4,458 union allselect 4,123 union allselect 4,90GoDECLARE @s NVARCHAR(4000),@i NVARCHAR(3),@s2 NVARCHAR(1000)Select TOP 1 @i=COUNT(*),@s='' from #T GROUP BY ID order by count(*) descWHILE @i>0    SELECT @s=N',[type'+@i+']'+@s,@i=@i-1SET @s2=STUFF(@s,1,1,'')EXEC( N'SELECT ID'+@s+N'FROM (select *, row=''type''+rtrim(row_number()over(partition by ID order by ID)) from #T) as apivot(max([type]) for row in('+@s2+'))as b')go/*ID    type1    type2    type3    type4    type51    23    12    103    NULL    NULL2    45    98    NULL    NULL    NULL4    1    104    458    123    90*/ 
 
[解决办法]
SQL code
if not object_id(N'Tempdb..#T') is null    drop table #TGoCreate table #T([id] int,[type] int)Insert #Tselect 1,23 union allselect 1,12 union allselect 1,103 union allselect 2,45 union allselect 2,98 union allselect 4,1 union allselect 4,104 union allselect 4,458 union allselect 4,123 union allselect 4,90Go--SELECT id,[type], dense_rank() OVER (PARTITION BY id ORDER BY [type]) FROM #TSELECT id,[1],[2],[3],[4],[5]FROM (SELECT id,[type], LB = dense_rank() OVER (PARTITION BY id ORDER BY [type]) FROM #T) APIVOT(MAX([type]) FOR LB IN ([1],[2],[3],[4],[5]))AS pvtORDER BY 1/*id          1           2           3           4           5----------- ----------- ----------- ----------- ----------- -----------1           12          23          103         NULL        NULL2           45          98          NULL        NULL        NULL4           1           90          104         123         458*/
[解决办法]
SQL code
if not object_id(N'Tempdb..#T') is null    drop table #TGoCreate table #T([id] int,[type] int)Insert #Tselect 1,23 union allselect 1,12 union allselect 1,103 union allselect 2,45 union allselect 2,98 union allselect 4,1 union allselect 4,104 union allselect 4,458 union allselect 4,123 union allselect 4,90Go--SELECT id,[type], dense_rank() OVER (PARTITION BY id ORDER BY [type]) FROM #TSELECT id,[type1],[type2],[type3],[type4],[type5]FROM (SELECT id,[type],LB = CASE LB WHEN 1 THEN 'type1'        WHEN 2 THEN 'type2'        WHEN 3 THEN 'type3'        WHEN 4 THEN 'type4'        WHEN 5 THEN 'type5'        ENDFROM (SELECT id,[type], LB = dense_rank() OVER (PARTITION BY id ORDER BY [type]) FROM #T) A) APIVOT(MAX([type]) FOR LB IN ([type1],[type2],[type3],[type4],[type5]))AS pvtORDER BY 1/*id          type1       type2       type3       type4       type5----------- ----------- ----------- ----------- ----------- -----------1           12          23          103         NULL        NULL2           45          98          NULL        NULL        NULL4           1           90          104         123         458*/