帮忙做一条语句

[zhangaipl]

A表
aa bb cc dd ee.........
1 1
2 1 2
3 1 2 3
4 1 2 3 4
.......

B表
aa value
1 1
2 1
2 2
3 1
3 2
3 3
........
请帮我把A表转化成B表。

[叶子]

declare @A表 table (aa int,bb int,cc int,dd int,ee int)
insert into @A表
select 1,1,null,null,null union all
select 2,1,2,null,null union all
select 3,1,2,3,null union all
select 4,1,2,3,4

SELECT * FROM 
(
	select aa,bb from @A表 UNION ALL
	select aa,cc from @A表 UNION ALL
	select aa,dd from @A表 UNION ALL
	select aa,ee from @A表 
) aa WHERE bb IS NOT NULL ORDER BY 1,2
/*
aa          bb
----------- -----------
1           1
2           1
2           2
3           1
3           2
3           3
4           1
4           2
4           3
4           4
*/

[--小F--]

动态行列转换。

[勿勿]

类是动态行列转换

[--小F--]

列转行
--> --> (Roy)生成測試數據
 
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go

--2000:

动态:

declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序

生成静态:
select * 
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all 
select [Student],[Course]='物理',[Score]=[物理] from Class union all 
select [Student],[Course]='英语',[Score]=[英语] from Class union all 
select [Student],[Course]='语文',[Score]=[语文] from Class)t 
order by [Student],[Course]

go
--2005:

动态:

declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student') 
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')

go
select 
    Student,[Course],[Score] 
from 
    Class 
unpivot 
    ([Score] for [Course] in([数学],[物理],[英语],[语文]))b

生成格式:
/*
Student Course Score
------- ------- -----------
李四      数学      77
李四      物理      85
李四      英语      65
李四      语文      65
张三      数学      87
张三      物理      90
张三      英语      82
张三      语文      78

(8 行受影响)
*/

[快溜]

select aa,bb as [value] from tb where bb is not null
union all
select aa,cc as [value] from tb where cc is not null
union all
select aa,dd as [value] from tb where dd is not null
.....

[pengxuan]

动态列转行