合并列值－合并为一列

问题需求：
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   5    8   3   6    9
要求语句灵活，不一定就这几行数据。

解决方案：
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    
—— —— —— —— —— —— —— —— ——
1      4      7      2      5      8      3      6      9
*/
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

/*
A1          B1          C1          A2          B2          C2          A3          B3          C3          
———– ———– ———– ———– ———– ———– ———– ———– ———–
1           2           3           4           5           6           7           8           9
*/
以上语句均可直接运行。