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

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

------解决方案--------------------
SQL code
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
*/

------解决方案--------------------
SQL code
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
*/

------解决方案--------------------
改改
SQL code
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
*/