1 数据准备：
Create table #temp(Col1 VARCHAR(20),col2 int)
insert #temp
select 'A',1
UNION ALL
select 'A',2
UNION ALL
select 'A',3
UNION ALL
select 'B',1
UNION ALL
select 'B',2
UNION ALL
select 'C',1
UNION ALL
select 'C',2
UNION ALL
select 'C',3
UNION ALL
select 'C',4
SELECT * FROM #temp
2 原始数据
Col1 col2
A 1
A 2
A 3
B 1
B 2
C 1
C 2
C 3
C 4
3 希望要的数据结果为
COL1 NM1 NM2 NM3 NM4
A 1 2 3 NULL
B 1 2 NULL NULL
C 1 2 3 4
4 简单描述
按照 co1中 进行分组，把其中的行数据 转化成列，并且依照分组中 行最多的数据作成列，列名自动生成
我现在为NM1,NM2.....,其他不足的列补NULL
------解决方案--------------------
Create table #temp(Col1 VARCHAR(20),col2 int)
insert #temp
select 'A',1
UNION ALL
select 'A',2
UNION ALL
select 'A',3
UNION ALL
select 'B',1
UNION ALL
select 'B',2
UNION ALL
select 'C',1
UNION ALL
select 'C',2
UNION ALL
select 'C',3
UNION ALL
select 'C',4
--SQL:
DECLARE @sql NVARCHAR(MAX),@colList1 NVARCHAR(MAX),@colList2 NVARCHAR(MAX)
SELECT
@colList1 = STUFF(
(
SELECT ','+QUOTENAME(LTRIM(col2))
FROM (SELECT DISTINCT COL2 FROM #temp) t
FOR XML PATH('')
),1,1,'')
,@colList2 =
(
SELECT ','+QUOTENAME(LTRIM(col2))+' NM'+LTRIM(col2)
FROM (SELECT DISTINCT COL2 FROM #temp) t
FOR XML PATH('')
)
SET @sql = N'
select col1'+@colList2+N' from #temp a
pivot
(max(col2) for col2 in('+ @colList1 +')) b
'
EXEC(@sql)
------解决方案--------------------
--添加了一临时表
select *,rn=ROW_NUMBER() over (partition by col1 order by col2) into #t from #temp
declare @s varchar(max)
select @s=ISNULL(@s+',','')+'MAX(case when rn='''+convert(varchar,rn)+''' then col2 end) as [NM'+convert(varchar,rn)+']'
from #t group by rn
set @s='select col1,'+@s+'from #t group by col1'
exec(@s)
drop table #t