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表。
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表。
--> --> (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 行受影响)
*/
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
.....
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,4SELECT * 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
*/