关于行列转换用pivot的问题

[bushy]

如何实现以下数据的行列转换：

--数据
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

[dawugui]

--sql 2000用子查询实现.

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) type5
from
(
  select t.* , px = (select count(1) from tb where id = t.id and type < t.type) + 1 from tb t
) m
group by id

[中国风]

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[type] int)
Insert #T
select 1,23 union all
select 1,12 union all
select 1,103 union all
select 2,45 union all
select 2,98 union all
select 4,1 union all
select 4,104 union all
select 4,458 union all
select 4,123 union all
select 4,90
Go

DECLARE @s NVARCHAR(4000),@i NVARCHAR(3)

Select TOP 1 @i=COUNT(*),@s='' from #T GROUP BY ID order by count(*) desc

WHILE @i>0
    SELECT @s=N',[type'+@i+']=max(case when Row='+@i+N' then [type] end)'+@s,@i=@i-1


EXEC(N'SELECT ID'+@s+N'
FROM (select *, row=row_number()over(partition by ID order by ID)from #T) as a
GROUP BY ID')
go
/*
ID	type1	type2	type3	type4	type5
1	23	12	103	NULL	NULL
2	45	98	NULL	NULL	NULL
4	1	104	458	123	90
*/

[dawugui]

--sql 2005用row_number()实现.

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) type5
from
(
  select t.* , px = row_number() over(partition by id order by type) from tb t
) m
group by id

[dawugui]

--sql 2000

create table tb(id int,  type int)
insert into tb values( 1  ,  23)
insert into tb values( 1  ,  12)
insert into tb values( 1  ,  103)
insert into tb values( 2  ,  45)
insert into tb values( 2  ,  98)
insert into tb values( 4  ,  1)
insert into tb values( 4  ,  104)
insert into tb values( 4  ,  458)
insert into tb values( 4  ,  123)
insert into tb values( 4  ,  90)
go

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) type5
from
(
  select t.* , px = (select count(1) from tb where id = t.id and type < t.type) + 1 from tb t
) m
group by id


drop table tb

/*
id          type1       type2       type3       type4       type5       
----------- ----------- ----------- ----------- ----------- ----------- 
1           12          23          103         NULL        NULL
2           45          98          NULL        NULL        NULL
4           1           90          104         123         458

（所影响的行数为 3 行）

警告: 聚合或其它 SET 操作消除了空值。
*/

--sql 2005

create table tb(id int,  type int)
insert into tb values( 1  ,  23)
insert into tb values( 1  ,  12)
insert into tb values( 1  ,  103)
insert into tb values( 2  ,  45)
insert into tb values( 2  ,  98)
insert into tb values( 4  ,  1)
insert into tb values( 4  ,  104)
insert into tb values( 4  ,  458)
insert into tb values( 4  ,  123)
insert into tb values( 4  ,  90)
go

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) type5
from
(
  select t.* , px = row_number() over(partition by id order by type) from tb t
) m
group by id


drop table tb

/*
id          type1       type2       type3       type4       type5
----------- ----------- ----------- ----------- ----------- -----------
1           12          23          103         NULL        NULL
2           45          98          NULL        NULL        NULL
4           1           90          104         123         458
警告: 聚合或其他 SET 操作消除了空值。

(3 行受影响)
*/

[中国风]

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[type] int)
Insert #T
select 1,23 union all
select 1,12 union all
select 1,103 union all
select 2,45 union all
select 2,98 union all
select 4,1 union all
select 4,104 union all
select 4,458 union all
select 4,123 union all
select 4,90
Go

DECLARE @s NVARCHAR(4000),@i NVARCHAR(3)

Select TOP 1 @i=COUNT(*),@s='' from #T GROUP BY ID order by count(*) desc

WHILE @i>0
    SELECT @s=N',[type'+@i+']'+@s,@i=@i-1
SET @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 a
pivot
(max([type]) for row in('+@s+') )as b')
go
/*
type1	type2	type3	type4	type5
1	12	103	NULL	NULL
2	98	NULL	NULL	NULL
4	104	458	123	90
*/

[中国风]

改改

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[type] int)
Insert #T
select 1,23 union all
select 1,12 union all
select 1,103 union all
select 2,45 union all
select 2,98 union all
select 4,1 union all
select 4,104 union all
select 4,458 union all
select 4,123 union all
select 4,90
Go





DECLARE @s NVARCHAR(4000),@i NVARCHAR(3),@s2 NVARCHAR(1000)

Select TOP 1 @i=COUNT(*),@s='' from #T GROUP BY ID order by count(*) desc

WHILE @i>0
    SELECT @s=N',[type'+@i+']'+@s,@i=@i-1
SET @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 a
pivot
(max([type]) for row in('+@s2+'))as b')
go
/*
ID	type1	type2	type3	type4	type5
1	23	12	103	NULL	NULL
2	45	98	NULL	NULL	NULL
4	1	104	458	123	90
*/

[一十七]

if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([id] int,[type] int)
Insert #T
select 1,23 union all
select 1,12 union all
select 1,103 union all
select 2,45 union all
select 2,98 union all
select 4,1 union all
select 4,104 union all
select 4,458 union all
select 4,123 union all
select 4,90
Go

--SELECT id,[type], dense_rank() OVER (PARTITION BY id ORDER BY [type]) FROM #T


SELECT 
id
,[1]
,[2]
,[3]
,[4]
,[5]
FROM 
(
SELECT id,[type], LB = dense_rank() OVER (PARTITION BY id ORDER BY [type]) FROM #T
) A
PIVOT
(
MAX([type]) FOR LB IN ([1],[2],[3],[4],[5])
)
AS pvt
ORDER BY 1
/*
id          1           2           3           4           5
----------- ----------- ----------- ----------- ----------- -----------
1           12          23          103         NULL        NULL
2           45          98          NULL        NULL        NULL
4           1           90          104         123         458
*/

PIVOT 解法

[一十七]

if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([id] int,[type] int)
Insert #T
select 1,23 union all
select 1,12 union all
select 1,103 union all
select 2,45 union all
select 2,98 union all
select 4,1 union all
select 4,104 union all
select 4,458 union all
select 4,123 union all
select 4,90
Go

--SELECT id,[type], dense_rank() OVER (PARTITION BY id ORDER BY [type]) FROM #T


SELECT 
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'
		END
FROM (SELECT id,[type], LB = dense_rank() OVER (PARTITION BY id ORDER BY [type]) FROM #T) A
) A
PIVOT
(
MAX([type]) FOR LB IN ([type1],[type2],[type3],[type4],[type5])
)
AS pvt
ORDER BY 1

/*

id          type1       type2       type3       type4       type5
----------- ----------- ----------- ----------- ----------- -----------
1           12          23          103         NULL        NULL
2           45          98          NULL        NULL        NULL
4           1           90          104         123         458
*/

PIVOT 解法 求分#17