高分求教，行列转换。

Question

tab1col1    col2   col3   col4   col5
1        132    3242   a       1
1        132    3242   b       2
3        134    3543   c       1
3        134    3543   d       2
3        134    3543   b       3
7        145    6743   e       1
7        145    6743   f       3结果集
col1    col2   col3   col4     a      b       c       d     e     f
1        132    3242   a       1      2      null    null  null  null
3        134    3543   c       null   3       1       2    null  null
7        145    6743   e       null   null   null    null   1     3tab1中行数未知，distinct col4 未知。即，结果集列数不定。已知通过多次自连接可行，但是不知道有没有简单的算法。

Answer 1

if object_id('tb') is not null
drop table tb
go
create table tb(col1 int,col2 int,col3 int,col4 varchar(20),col5 int)
insert into tb select 1,132,3242,'a',1
insert into tb select 1,132,3242,'b',2
insert into tb select 3,134,3543,'c',1
insert into tb select 3,134,3543,'d',2
insert into tb select 3,134,3543,'b',3
insert into tb select 7,145,3543,'e',1
insert into tb select 7,145,3543,'f',3declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'max(case when col4='''+col4+''' then col5 else null end) as ['+col4+']'
from tb group by col4
exec('select col1,col2,col3,col4=(select col4 from tb where col1=t.col1 and col5=''1''),'+@sql+' from tb t group by col1,col2,col3')col1 col2 col3 col4 a b c d e f
1 132 3242 a 1 2 NULL NULL NULL NULL
3 134 3543 c NULL 3 1 2 NULL NULL
7 145 3543 e NULL NULL NULL NULL 1 3

Answer 2

create table #(col1 int,col2 int,col3 int,col4 varchar(20),col5 int)
insert into # select 1,132,3242,'a',1
insert into # select 1,132,3242,'b',2
insert into # select 3,134,3543,'c',1
insert into # select 3,134,3543,'d',2
insert into # select 3,134,3543,'b',3
insert into # select 7,145,3543,'e',1
insert into # select 7,145,3543,'f',3
借楼上数据一用
declare @s varchar(8000)
select @s='select col1,col2,col3,col4=(select top 1 col4 from # where col1=a.col1 ) '
select @s=isnull(@s+',','')+'max(case when col4='''+col4+''' then col5 else null end )as '''+col4+''''
from # group by col4
exec(@s+' from # a group by col1,col2,col3')/*
col1        col2        col3        col4                 a           b           c           d           e           f           
----------- ----------- ----------- -------------------- ----------- ----------- ----------- ----------- ----------- ----------- 
1           132         3242        a                    1           2           NULL        NULL        NULL        NULL
3           134         3543        c                    NULL        3           1           2           NULL        NULL
7           145         3543        e                    NULL        NULL        NULL        NULL        1           3警告: 聚合或其它 SET 操作消除了空值。
*/

Answer 3

create table #(col1 int,col2 int,col3 int,col4 varchar(20),col5 int)
insert into # select 1,132,3242,'a',1
insert into # select 1,132,3242,'b',2
insert into # select 3,134,3543,'c',1
insert into # select 3,134,3543,'d',2
insert into # select 3,134,3543,'b',3
insert into # select 7,145,3543,'e',1
insert into # select 7,145,3543,'f',3declare @s varchar(8000)
select @s='select col1,col2,col3,col4=(select top 1 col4 from # where col1=a.col1 ) '
select @s=isnull(@s+',','')+'max(case when col4='''+col4+''' then col5 else null end )as '''+col4+''''
from # group by col4
exec(@s+' from # a group by col1,col2,col3')

Answer 4

create table tb(col1 int,col2 int,col3 int,col4 varchar(20),col5 int)
insert into tb select 1,132,3242,'a',1
insert into tb select 1,132,3242,'b',2
insert into tb select 3,134,3543,'c',1
insert into tb select 3,134,3543,'d',2
insert into tb select 3,134,3543,'b',3
insert into tb select 7,145,3543,'e',1
insert into tb select 7,145,3543,'f',3
declare @sql varchar(8000)
set @sql='select col1,col2,col3,col4=(select top 1 col4 from tb where col1=a.col1 order by col5)'
select @sql=@sql+',['+col4+']=max(case col4 when '''+col4+''' then col5 else null end)' from 
(select distinct col4 from tb)a
set @sql=@sql+' from tb a group by col1,col2,col3'
exec(@sql)

Answer 5

行列互转_整理贴3
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html动态SQL

Answer 6

--我也搞一个,搞得比较复杂.只贡参考:
create table #(col1 int,col2 int,col3 int,col4 varchar(20),col5 int)
insert into # select 1,132,3242,'a',1
insert into # select 1,132,3242,'b',2
insert into # select 3,134,3543,'c',1
insert into # select 3,134,3543,'d',2
insert into # select 3,134,3543,'b',3
insert into # select 7,145,3543,'e',1
insert into # select 7,145,3543,'f',3select ta.col1,col2,col3,col4,a,b,c,d,e,f from
(
select * from # 
pivot
(sum(col5)
 for col4 in ([a],[b],[c],[d],[e],[f])
) as pt
) ta
left join 
( select ta.col1,ta.col4 from # ta,
(
select col1,col5=min(col5) 
from #
group by col1) tb where ta.col1=tb.col1 and ta.col5=tb.col5
) tb
on ta.col1=tb.col1
/*
col1        col2        col3        col4                 a           b           c           d           e           f
----------- ----------- ----------- -------------------- ----------- ----------- ----------- ----------- ----------- -----------
1           132         3242        a                    1           2           NULL        NULL        NULL        NULL
3           134         3543        c                    NULL        3           1           2           NULL        NULL
7           145         3543        e                    NULL        NULL        NULL        NULL        1           3(3 行受影响)
*/

Answer 7

create table tb(col1 int,col2 int,col3 int,col4 varchar(20),col5 int)
insert into tb select 1,132,3242,'a',1
insert into tb select 1,132,3242,'b',2
insert into tb select 3,134,3543,'c',1
insert into tb select 3,134,3543,'d',2
insert into tb select 3,134,3543,'b',3
insert into tb select 7,145,3543,'e',1
insert into tb select 7,145,3543,'f',3declare @sql nvarchar(4000)
set @sql='select col1,col2,col3,col4'
select @sql=@sql+N','+quotename(col4)+N'=sum(case when col4='+quotename(col4,'''')+N' then col5 else 0 end) ' from (select distinct col4 from tb) B
set @sql=@sql+N' from tb group by col1,col2,col3,col4'
exec (@sql)
col1        col2        col3        col4                 a           b           c           d           e           f           
----------- ----------- ----------- -------------------- ----------- ----------- ----------- ----------- ----------- ----------- 
1           132         3242        a                    1           0           0           0           0           0
1           132         3242        b                    0           2           0           0           0           0
3           134         3543        b                    0           3           0           0           0           0
3           134         3543        c                    0           0           1           0           0           0
3           134         3543        d                    0           0           0           2           0           0
7           145         3543        e                    0           0           0           0           1           0
7           145         3543        f                    0           0           0           0           0           3

Answer 8

/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂整理人：Roy
日期:2008.06.06
******************************************************************************************************************************************************/--1、行互列
--> --> (Roy)生成測試數據
 
if not object_id('Class') is null
    drop table Class
Go
Create table Class([student] nvarchar(2),[subject] nvarchar(2),[grade] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85 
Go
--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([subject])+'=max(case when [subject]='+quotename([subject],'''')+' then [grade] else 0 end)'
from Class group by[subject]
exec('select [student]'+@s+' from Class group by [student]')
生成静态:select 
    [student],
    [数学]=max(case when [subject]='数学' then [grade] else 0 end),
    [物理]=max(case when [subject]='物理' then [grade] else 0 end),
    [英语]=max(case when [subject]='英语' then [grade] else 0 end),
    [语文]=max(case when [subject]='语文' then [grade] else 0 end) 
from 
    Class 
group by [student]GO
动态:declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([subject]) from Class group by[subject]
exec('select * from Class pivot (max([grade]) for [subject] in('+@s+'))b')生成静态:
select * 
from 
    Class 
pivot 
    (max([grade]) for [subject] in([数学],[物理],[英语],[语文]))b生成格式:
/*
student 数学          物理          英语          语文
------- ----------- ----------- ----------- -----------
李四      77          85          65          65
张三      87          90          82          78(2 行受影响)
*/------------------------------------------------------------------------------------------
go
--加上总成绩(学科平均分)--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([subject])+'=max(case when [subject]='+quotename([subject],'''')+' then [grade] else 0 end)'
from Class group by[subject]
exec('select [student]'+@s+',[总成绩]=sum([grade])  from Class group by [student]')--加多一列(学科平均分用avg([grade]))生成动态:select 
    [student],
    [数学]=max(case when [subject]='数学' then [grade] else 0 end),
    [物理]=max(case when [subject]='物理' then [grade] else 0 end),
    [英语]=max(case when [subject]='英语' then [grade] else 0 end),
    [语文]=max(case when [subject]='语文' then [grade] else 0 end),
    [总成绩]=sum([grade]) --加多一列(学科平均分用avg([grade]))
from 
    Class 
group by [student]go--2005方法:动态:declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([subject]) from Class group by[subject] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [student],'+@s+',[总成绩] from (select *,[总成绩]=sum([grade])over(partition by [student]) from Class) a 
pivot (max([grade]) for [subject] in('+@s+'))b ')生成静态:select 
    [student],[数学],[物理],[英语],[语文],[总成绩] 
from 
    (select *,[总成绩]=sum([grade])over(partition by [student]) from Class) a --平均分时用avg([grade])
pivot 
    (max([grade]) for [subject] in([数学],[物理],[英语],[语文]))b 生成格式:/*
student 数学          物理          英语          语文          总成绩
------- ----------- ----------- ----------- ----------- -----------
李四      77          85          65          65          292
张三      87          90          82          78          337(2 行受影响)
*/go--2、列转行
--> --> (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],[subject]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[grade]='+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],[subject]')--增加一个排序生成静态:
select * 
from (select [student],[subject]='数学',[grade]=[数学] from Class union all 
select [student],[subject]='物理',[grade]=[物理] from Class union all 
select [student],[subject]='英语',[grade]=[英语] from Class union all 
select [student],[subject]='语文',[grade]=[语文] from Class)t 
order by [student],[subject]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,[subject],[grade] from Class unpivot ([grade] for [subject] in('+@s+'))b')go
select 
    student,[subject],[grade] 
from 
    Class 
unpivot 
    ([grade] for [subject] in([数学],[物理],[英语],[语文]))b生成格式:
/*
student subject grade
------- ------- -----------
李四      数学      77
李四      物理      85
李四      英语      65
李四      语文      65
张三      数学      87
张三      物理      90
张三      英语      82
张三      语文      78(8 行受影响)
*/

Answer 9

--> --> (Andy)生成测试数据 2008-07-03 
Set Nocount On
if not object_id('Test') is null
drop table Test
Go
Create table Test([col1] int,[col2] int,[col3] Datetime,[col4] nvarchar(1),[col5] int)
Insert Test
select 1,132,'3242',N'a',1 union all
select 1,132,'3242',N'b',2 union all
select 3,134,'3543',N'c',1 union all
select 3,134,'3543',N'd',2 union all
select 3,134,'3543',N'b',3 union all
select 7,145,'6743',N'e',1 union all
select 7,145,'6743',N'f',3
Go
Declare @sql nvarchar(Max)Select 
@sql=Isnull(@sql+',','Select * From test a Pivot(Max(col5) For col4 In(')+Quotename(col4) 
From Test
Group by col4Exec( @sql+')) b')/*
col1        col2        col3                    a           b           c           d           e           f
----------- ----------- ----------------------- ----------- ----------- ----------- ----------- ----------- -----------
1           132         3242-01-01 00:00:00.000 1           2           NULL        NULL        NULL        NULL
3           134         3543-01-01 00:00:00.000 NULL        3           1           2           NULL        NULL
7           145         6743-01-01 00:00:00.000 NULL        NULL        NULL        NULL        1           3
*/

Answer 10

不好意思，主动生成数据中，定义类型错误了，还漏1列col4，纠正下：--> --> (Andy)生成测试数据 2008-07-03 
Set Nocount On
if not object_id('Test') is null
drop table Test
Go
Create table Test([col1] int,[col2] int,[col3] int,[col4] nvarchar(1),[col5] int)
Insert Test
select 1,132,'3242',N'a',1 union all
select 1,132,'3242',N'b',2 union all
select 3,134,'3543',N'c',1 union all
select 3,134,'3543',N'd',2 union all
select 3,134,'3543',N'b',3 union all
select 7,145,'6743',N'e',1 union all
select 7,145,'6743',N'f',3
Go
Declare @sql nvarchar(Max)Select 
@sql=Isnull(@sql+',','Select * From test a Outer Apply(Select top(1)  col4 From test Where [col1]=a.[col1] And [col2]=a.[col2] And [col3]=a.[col3])b Pivot(Max(col5) For a.col4 In(')+Quotename(col4) 
From Test
Group by col4exec( @sql+')) c')/*
col1        col2        col3        col4 a           b           c           d           e           f
----------- ----------- ----------- ---- ----------- ----------- ----------- ----------- ----------- -----------
1           132         3242        a    1           2           NULL        NULL        NULL        NULL
3           134         3543        c    NULL        3           1           2           NULL        NULL
7           145         6743        e    NULL        NULL        NULL        NULL        1           3
*/

Answer 11

declare @sql nvarchar(4000)
set @sql=N'select col1,col2,col3,col4=(select top 1 col4 from tb where col1=a.col1 order by col5)'
select @sql=@sql+N','+quotename(col4)+N'=sum(case when col4 ='+quotename(col4,'''')+N' then col5 else null end)' from (select distinct col4 from tb)A
set @sql=@sql+N' from tb a group by col1,col2,col3'
exec sp_executesql @sqlcol1        col2        col3        col4                 a           b           c           d           e           f           
----------- ----------- ----------- -------------------- ----------- ----------- ----------- ----------- ----------- ----------- 
1           132         3242        a                    1           2           NULL        NULL        NULL        NULL
3           134         3543        c                    NULL        3           1           2           NULL        NULL
7           145         3543        e                    NULL        NULL        NULL        NULL        1           3（所影响的行数为 3 行）警告: 聚合或其它 SET 操作消除了空值。