问题需求： 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 */ 以上语句均可直接运行。
转载于:https://www.cnblogs.com/Gaojier/archive/2010/04/08/2783570.html
