行列转换的结果有多条记录，怎么写？
Table:
Date Budget SO Invoice
2012-06-01 100 50 30
2012-06-02 120 40 60
2012-06-03 90 60 40
2012-06-04 110 80 60
........

我想要的结果是：
date 2012-06-01 2012-06-02 2012-06-03 2012-06-04 ......
budget 100 120 90 110
SO 50 40 60 80
Invoice 30 60 40 60


[解决办法]

SQL code

--行列互转--摘自中国风博客，引用请标明内容来源--1、行换列if object_id('Class') is not  null    drop table ClassGoCreate table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)Insert Classselect N'张三',N'语文',78 union allselect N'张三',N'数学',87 union allselect N'张三',N'英语',82 union allselect N'张三',N'物理',90 union allselect N'李四',N'语文',65 union allselect N'李四',N'数学',77 union allselect N'李四',N'英语',65 union allselect N'李四',N'物理',85 Go--2000方法:--动态:declare @s nvarchar(4000)set @s=''Select @s=@s+','+quotename([Course])+'=max(case when [Course]='           +quotename([Course],'''')+' then [Score] else 0 end)'from     Class group by[Course]--select @sexec('select [Student]'+@s+' from Class group by [Student]')--生成静态:select     [Student],    [数学]=max(case when [Course]='数学' then [Score] else 0 end),    [物理]=max(case when [Course]='物理' then [Score] else 0 end),    [英语]=max(case when [Course]='英语' then [Score] else 0 end),    [语文]=max(case when [Course]='语文' then [Score] else 0 end) from     Class group by [Student]GO--动态:declare @s nvarchar(4000)Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]select @sexec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')--生成静态:select * from     Class pivot     (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b--生成格式:/*Student 数学         物理         英语         语文------- ----------- ----------- ----------- -----------李四     77          85          65          65张三     87          90          82          78(2 行受影响)*/go--加上总成绩(学科平均分)--2000方法:--动态:declare @s nvarchar(4000)set @s=''Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+'then [Score] else 0 end)'from Class group by[Course]exec('select [Student]'+@s+',[总成绩]=sum([Score])  from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select     [Student],    [数学]=max(case when [Course]='数学' then [Score] else 0 end),    [物理]=max(case when [Course]='物理' then [Score] else 0 end),    [英语]=max(case when [Course]='英语' then [Score] else 0 end),    [语文]=max(case when [Course]='语文' then [Score] else 0 end),    [总成绩]=([Score]) --加多一列(学科平均分用avg([Score]))from     Class group by [Student]go--2005方法:动态:declare @s nvarchar(4000)Select  @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a pivot (max([Score]) for [Course] in('+@s+'))b ')--生成静态:select     [Student],[数学],[物理],[英语],[语文],[总成绩] from     (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])pivot     (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*Student 数学         物理         英语         语文         总成绩------- ----------- ----------- ----------- ----------- -----------李四     77          85          65          65          292张三     87          90          82          78          337(2 行受影响)*/go--2、列转行 if not object_id('Class') is null    drop table ClassGoCreate table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)Insert Classselect N'李四',77,85,65,65 union allselect N'张三',87,90,82,78Go--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 Colidexec('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 Colidexec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')goselect     Student,[Course],[Score] from     Class unpivot     ([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式:/*Student Course Score------- ------- -----------李四     数学     77李四     物理     85李四     英语     65李四     语文     65张三     数学     87张三     物理     90张三     英语     82张三     语文     78*/--用动态的就好了 
 
[解决办法]
SQL code
create table mic(cDate date, Budget int, SO int, Invoice int)insert into micselect '2012-06-01', 100, 50, 30 union allselect '2012-06-02', 120, 40, 60 union allselect '2012-06-03', 90, 60, 40 union allselect '2012-06-04', 110, 80, 60select c 'date',[2012-06-01],[2012-06-02],[2012-06-03],[2012-06-04]from(select cDate,c,v from mic a unpivot(v for c in([Budget],[SO],[Invoice])) u) bpivot(max(v) for cDate in([2012-06-01],[2012-06-02],[2012-06-03],[2012-06-04])) p/*date       2012-06-01  2012-06-02  2012-06-03  2012-06-04---------- ----------- ----------- ----------- -----------Budget     100         120         90          110Invoice    30          60          40          60SO         50          40          60          80(3 row(s) affected)*/