SQL 行转列

[许晨辰]

表是这样的 Y2004 Y2005 Y2006 Y2007 Y2008
21 23 32 67 67
32 34 98 43 234

要转换成这样
Y2004 21 32
Y2005 23 34
Y2006 32 98
Y2007 67 43
Y2008 67 234

[dawugui]

90度行列转换参考如下:


/*
标题：90度旋转行列转换之一
作者：爱新觉罗.毓华(十八年风雨，守得冰山雪莲花开)
时间：2010-05-08
地点：重庆航天职业学院
说明：无
*/
/*
数据库中tb表格如下
月份 工资 福利 奖金
1月 100 200 300
2月 110 210 310
3月 120 220 320
4月 130 230 330

我想得到的结果是

项目 1月 2月 3月 4月
工资 100 110 120 130
福利 200 210 220 230
奖金 300 310 320 330

就是说完全把表格的行列颠倒，有点像那种旋转矩阵，请问如何用sql 语句实现？
*/

/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/
create proc p_zj
       @tbname sysname, --要处理的表名
       @fdname sysname, --做为转换的列名
       @new_fdname sysname='' --为转换后的列指定列名
as
declare @s1 varchar(8000) , @s2 varchar(8000),
        @s3 varchar(8000) , @s4 varchar(8000),
        @s5 varchar(8000) , @i varchar(10)
select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'
select @s1 = @s1 + ',@' + @i + ' varchar(8000)',
       @s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''
       else @new_fdname + '=' end + '''''' + name + '''''''',
       @s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname + 
       ']+'']=''+cast([' + name + '] as varchar) from [' + @tbname + ']',
       @s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,
       @s5 = @s5 + '+'' union all ''+@' + @i,
       @i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name<>@fdname

select @s1=substring(@s1,2,8000),
       @s2=substring(@s2,2,8000),
       @s4=substring(@s4,2,8000),
       @s5=substring(@s5,16,8000)
exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '
exec(' + @s5 + ')')
go

--创建测试数据
create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test 
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
go

--用上面的存储过程测试：
exec p_zj 'Test', '月份' , '项目'

drop table Test
drop proc p_zj

/*
项目      1月      2月      3月      4月
--------  ------   -------- -------- --------
奖金      300      310      320      330
工资      100      110      120      130
福利      200      210      220      230

（所影响的行数为 3 行）
*/

--SQL2005静态写法
--创建测试数据
create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
go

SELECT * FROM 
(
  SELECT 考核月份,月份,金额 FROM 
     (SELECT 月份, 工资, 福利, 奖金 FROM Test) p
  UNPIVOT
     (金额 FOR 考核月份 IN (工资, 福利, 奖金))AS unpvt
) T
PIVOT
(MAX(金额) FOR 月份 in ([1月],[2月],[3月],[4月]))AS pt

drop table test

/*
项目      1月      2月      3月      4月
--------  ------   -------- -------- --------
奖金      300      310      320      330
工资      100      110      120      130
福利      200      210      220      230

(3 行受影响)
*/

[开着拖拉机泡妞]

列转行

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
*/
--楼主参考这个

[许晨辰]

怎么都这么复杂，就不能搞点简单的吗？

[开着拖拉机泡妞]

[Quote=引用 3 楼 xcc5800321 的回复:]

怎么都这么复杂，就不能搞点简单的吗？
[/Quote]

简单的还没发现，楼主可以想想，想出来了就不错了

[百年树人]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Y2004] int,[Y2005] int,[Y2006] int,[Y2007] int,[Y2008] int)
insert [tb]
select 21,23,32,67,67 union all
select 32,34,98,43,234
go

declare @sql1 varchar(max),@sql2 varchar(max)

select @sql1=isnull(@sql1+',','')
  +'sum(case when rn='+ltrim(rn)+' then yy else 0 end) as [val'+ltrim(rn)+']'
from
(select rn=row_number() over(order by getdate()) from tb) t

select @sql2=isnull(@sql2+' union all ','')
+' select '''+name+''' as [yy],'+replace(@sql1,'yy','['+name+']')
+' from (select rn=row_number() over(order by getdate()),* from tb) a'
from
(select name from sys.columns where object_id=object_id('tb')) t

exec (@sql2)

/**
yy    val1        val2
----- ----------- -----------
Y2004 21          32
Y2005 23          34
Y2006 32          98
Y2007 67          43
Y2008 67          234

(5 行受影响)
**/

[许晨辰]

强悍，谢谢！