问题需求：
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
*/
以上语句均可直接运行。
