合并列值－合并为一列

问题需求：
create table tab(A int,B int,C int)
insert tab
select 1,2,3
union all
select 4,5,6
union all
select 7,8,9
select * from tab
A B C
———– ———– ———–
1 2 3
4 5 6
7 8 9

要求结果为：
A1 A2 A3 B1 B2 B3 C1 C2 C3
1 4 7 2 58 3 69
要求语句灵活，不一定就这几行数据。

解决方案：
1、使用临时表
–> 测试数据: #
if object_id(‘tempdb.dbo.#’) is not null drop table #
create table # (A int,B int,C int)
insert into #
select 1,2,3 union all
select 4,5,6 union all
select 7,8,9

if object_id(‘tempdb.dbo.#T’) is not null drop table #T
select id=identity(int,1,1),* into #T from #

declare @SQL varchar(8000)
–select a.name+ltrim(b.id),b.* from tempdb.dbo.syscolumns a, #T b where a.id = object_id(‘tempdb.dbo.#T’) and name <> ‘id’ order by a.colid
select @SQL = isnull(@SQL+’,',”)+a.name+ltrim(b.id)+’=max(case id when ‘+ltrim(b.id)+’ then ‘+a.name+’ end)’ from tempdb.dbo.syscolumns a, #T b where a.id = object_id(‘tempdb.dbo.#T’) and name <> ‘id’ order by a.colid
exec (’select ‘+@SQL+’ from #T’)

/*
A1 A2 A3 B1 B2 B3 C1 C2 C3
—— —— —— —— —— —— —— —— ——
147258369
*/
2、使用sql语句：
create table tab(A int,B int,C int)
insert tab
select 1,2,3
union all
select 4,5,6
union all
select 7,8,9

declare @sql varchar(8000)
set @sql = ’select ‘
select @sql = @sql + ‘, max(case px when ”’ + cast(px as varchar) + ”’ then A else 0 end) [A' + cast(px as varchar) + '] ‘
+ ‘, max(case px when ”’ + cast(px as varchar) + ”’ then B else 0 end) [B' + cast(px as varchar) + '] ‘
+ ‘, max(case px when ”’ + cast(px as varchar) + ”’ then C else 0 end) [C' + cast(px as varchar) + '] ‘
from (select distinct px from (select * , px = (select count(1) from tab where A < t.A or (A = t.A and B < t.B) or (A = t.A and B = t.B and C < t.C)) + 1 from tab t) m) as a
set @sql = @sql + ‘ from (select * , px = (select count(1) from tab where A < t.A or (A = t.A and B < t.B) or (A = t.A and B = t.B and C < t.C)) + 1 from tab t) m’
set @sql = replace(@sql , ’select , max’ , ’select max’)
exec(@sql)

drop table tab

/*
A1B1C1A2B2C2A3B3C3
———– ———– ———– ———– ———– ———– ———– ———– ———–
1 2 3 4 5 6 7 8 9
*/
以上语句均可直接运行。