typeid name
1 A
1 B
1 C
2 C
2 D
3 A
3 B
3 D
3 E
4 E
变成下表B：
id name1 name2 name3 name4
1 A B C
2 C D C
3 A B D E
4 E
表A的数据是通过其他的查询得来的，不固定
6 个解决方案
#1
和你要的结果有点差异，但是个思路，可以继续完善
if object_id('tempdb..#tb') is not null DROP TABLE #tb
;WITH tb(typeid,[name]) as (
select 1,'A' UNION ALL
select 1,'B' UNION ALL
select 1,'C' UNION ALL
select 2,'C' UNION ALL
select 2,'D' UNION ALL
select 3,'A' UNION ALL
select 3,'B' UNION ALL
select 3,'D' UNION ALL
select 3,'E' UNION ALL
select 4,'E'
)
select *,'name'+convert(varchar,row_number()over(PARTITION BY [typeid] ORDER BY [name])) as rn_name into #tb from tb
declare @sql2 nvarchar(max)
select @sql2 = isnull(@sql2+',','') + [rn_name] from #tb group by [rn_name]
print @sql2
exec('select *
from (select * from #tb) a
pivot (max([name]) for [rn_name] in ('+@sql2+')) b ')
/*
typeid name1 name2 name3 name4
1 A B C NULL
2 C D NULL NULL
3 A B D E
4 E NULL NULL NULL
*/
#2
2 里面就只有cd 何来CDC啊？
#3
额，手误，只有CD
#4
create table 表A(typeid int, name char(1))
insert into 表A
select 1, 'A' union all
select 1, 'B' union all
select 1, 'C' union all
select 2, 'C' union all
select 2, 'D' union all
select 3, 'A' union all
select 3, 'B' union all
select 3, 'D' union all
select 3, 'E' union all
select 4, 'E'
declare @tsql1 varchar(2000),@tsql2 varchar(2000),@tsql3 varchar(6000)
select @tsql1=isnull(@tsql1+',','')+'['+rtrim(number)+']',
@tsql2=isnull(@tsql2+',','')+'isnull(['+rtrim(number)+'],'''') ''name'+rtrim(number)+''''
from master.dbo.spt_values
where type=N'P' and number>=1
and number<=(select top 1 count(1) from 表A group by typeid order by count(1) desc)
select @tsql3='select typeid ''id'','+@tsql2
+' from (select typeid,name,row_number() over(partition by typeid order by getdate()) ''rn'' from 表A) t '
+' pivot(max(name) for rn in('+@tsql1+')) u '
exec(@tsql3)
/*
id name1 name2 name3 name4
----------- ----- ----- ----- -----
1 A B C
2 C D
3 A B D E
4 E
(4 row(s) affected)
*/
#5
蔡总，我来了。
#6
;WITH tb(typeid,[name]) as (
select 1,'A' UNION ALL
select 1,'B' UNION ALL
select 1,'C' UNION ALL
select 2,'C' UNION ALL
select 2,'D' UNION ALL
select 3,'A' UNION ALL
select 3,'B' UNION ALL
select 3,'D' UNION ALL
select 3,'E' UNION ALL
select 4,'E'
)
SELECT * INTO #Tmp FROM tb
DECLARE @Sql NVARCHAR(max)=''
SELECT @Sql = @Sql + ',MAX(CASE WHEN a.RNr = '+RTRIM(a.RNr) + ' THEN a.name ELSE NULL END) AS name'+RTRIM(a.RNr) + CHAR(10)
FROM (SELECT * ,
ROW_NUMBER() OVER (PARTITION BY typeid ORDER BY name) AS RNr
FROM #Tmp)a
GROUP BY a.RNr
SELECT @Sql = '
SELECT a.typeid
'+@Sql+'
FROM (SELECT * ,
ROW_NUMBER() OVER (PARTITION BY typeid ORDER BY name) AS RNr
FROM #Tmp) a
GROUP BY a.typeid
'
EXEC (@Sql)
#1
和你要的结果有点差异，但是个思路，可以继续完善
if object_id('tempdb..#tb') is not null DROP TABLE #tb
;WITH tb(typeid,[name]) as (
select 1,'A' UNION ALL
select 1,'B' UNION ALL
select 1,'C' UNION ALL
select 2,'C' UNION ALL
select 2,'D' UNION ALL
select 3,'A' UNION ALL
select 3,'B' UNION ALL
select 3,'D' UNION ALL
select 3,'E' UNION ALL
select 4,'E'
)
select *,'name'+convert(varchar,row_number()over(PARTITION BY [typeid] ORDER BY [name])) as rn_name into #tb from tb
declare @sql2 nvarchar(max)
select @sql2 = isnull(@sql2+',','') + [rn_name] from #tb group by [rn_name]
print @sql2
exec('select *
from (select * from #tb) a
pivot (max([name]) for [rn_name] in ('+@sql2+')) b ')
/*
typeid name1 name2 name3 name4
1 A B C NULL
2 C D NULL NULL
3 A B D E
4 E NULL NULL NULL
*/
#2
2 里面就只有cd 何来CDC啊？
#3
额，手误，只有CD
#4
create table 表A(typeid int, name char(1))
insert into 表A
select 1, 'A' union all
select 1, 'B' union all
select 1, 'C' union all
select 2, 'C' union all
select 2, 'D' union all
select 3, 'A' union all
select 3, 'B' union all
select 3, 'D' union all
select 3, 'E' union all
select 4, 'E'
declare @tsql1 varchar(2000),@tsql2 varchar(2000),@tsql3 varchar(6000)
select @tsql1=isnull(@tsql1+',','')+'['+rtrim(number)+']',
@tsql2=isnull(@tsql2+',','')+'isnull(['+rtrim(number)+'],'''') ''name'+rtrim(number)+''''
from master.dbo.spt_values
where type=N'P' and number>=1
and number<=(select top 1 count(1) from 表A group by typeid order by count(1) desc)
select @tsql3='select typeid ''id'','+@tsql2
+' from (select typeid,name,row_number() over(partition by typeid order by getdate()) ''rn'' from 表A) t '
+' pivot(max(name) for rn in('+@tsql1+')) u '
exec(@tsql3)
/*
id name1 name2 name3 name4
----------- ----- ----- ----- -----
1 A B C
2 C D
3 A B D E
4 E
(4 row(s) affected)
*/
#5
蔡总，我来了。
#6
;WITH tb(typeid,[name]) as (
select 1,'A' UNION ALL
select 1,'B' UNION ALL
select 1,'C' UNION ALL
select 2,'C' UNION ALL
select 2,'D' UNION ALL
select 3,'A' UNION ALL
select 3,'B' UNION ALL
select 3,'D' UNION ALL
select 3,'E' UNION ALL
select 4,'E'
)
SELECT * INTO #Tmp FROM tb
DECLARE @Sql NVARCHAR(max)=''
SELECT @Sql = @Sql + ',MAX(CASE WHEN a.RNr = '+RTRIM(a.RNr) + ' THEN a.name ELSE NULL END) AS name'+RTRIM(a.RNr) + CHAR(10)
FROM (SELECT * ,
ROW_NUMBER() OVER (PARTITION BY typeid ORDER BY name) AS RNr
FROM #Tmp)a
GROUP BY a.RNr
SELECT @Sql = '
SELECT a.typeid
'+@Sql+'
FROM (SELECT * ,
ROW_NUMBER() OVER (PARTITION BY typeid ORDER BY name) AS RNr
FROM #Tmp) a
GROUP BY a.typeid
'
EXEC (@Sql)