原来数据表A里的数据如下
ID T8 T9 T10 T11
1 1 1 1 1
2 1 1 2 2
3 3 3 3 3
5 5 5 15 1 1
希望把A数据表的内容转换为这样
ID GG Value
1 T8 1
1 T9 1
1 T10 1
1 T11 1
2 T8 1
2 T9 1
2 T10 2
2 T11 2
3 T8 3
3 T9 3
3 T10 3
3 T11 3
5 T8 5
5 T9 5
5 T10 15
5 T11 11
求高手看看怎么写这样的SQL语句啊？
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-04-10 14:36:36
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[T8] int,[T9] int,[T10] int,[T11] int)
insert [huang]
select 1,1,1,1,1 union all
select 2,1,1,2,2 union all
select 3,3,3,3,3 union all
select 5,5,5,15,11
--------------生成数据--------------------------
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [id],[gg]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[value]='+quotename(Name)+' from [huang]'
from syscolumns where ID=object_id('huang') and Name not in('id')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by ID,value')--增加一个排序
----------------结果----------------------------
/*
id gg value
----------- ---- -----------
1 T8 1
1 T9 1
1 T10 1
1 T11 1
2 T9 1
2 T8 1
2 T11 2
2 T10 2
3 T10 3
3 T11 3
3 T8 3
3 T9 3
5 T9 5
5 T8 5