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
SELECT ID,[Value] FROM [table] UNPIVOT([Value] FOR [TYPE]IN([S1],[S2],[S3]))U 只要你列数固定可以用以上,如果不固定就用动态处理
with cte as (select '001' as id,1 as s1,2 as s2,3 as s3 ) select 'id'as id, types from cte unpivot (qty for types in(s1,s2,s3))as p
列轉行 sql server 2005及以上版本可以採取 unpivot()
表格如图所示,按教程写如下的代码 select * from (select [dealereturnid],DealereturnGoodsID,goods_no,colorid,[chima]='s1',[sl]=s1 from #chuhuo union all select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s2',sl=s2 from #chuhuo union all select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s3',sl=s3 from #chuhuo union all select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s4',sl=s4 from #chuhuo union all select dealereturnid DealereturnGoodsID,goods_no,colorid,chima='s5',sl=s5 from #chuhuo union all select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s6',sl=s6 from #chuhuo union all select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s7',sl=s7 from #chuhuo union all select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s8',sl=s8 from #chuhuo union all select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s9',sl=s9 from #chuhuo union all select dealereturnid DealereturnGoodsID,goods_no,colorid,chima='s10',sl=s10 from #chuhuo union all select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s11',sl=s11 from #chuhuo union all select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s12',sl=s12 from #chuhuo ) torder by dealereturnid ,DealereturnGoodsID,goods_no,colorid,chima报错信息如下: 使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。单独一条一条执行语句没有错
--> --> (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 行受影响)
*/行列互转_整理贴3
http://bbs.csdn.net/topics/240002706
ID,[Value]
FROM
[table] UNPIVOT([Value] FOR [TYPE]IN([S1],[S2],[S3]))U
只要你列数固定可以用以上,如果不固定就用动态处理
(select '001' as id,1 as s1,2 as s2,3 as s3 )
select 'id'as id, types from cte unpivot
(qty for types in(s1,s2,s3))as p
表格如图所示,按教程写如下的代码
select * from
(select [dealereturnid],DealereturnGoodsID,goods_no,colorid,[chima]='s1',[sl]=s1 from #chuhuo
union all
select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s2',sl=s2 from #chuhuo
union all
select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s3',sl=s3 from #chuhuo
union all
select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s4',sl=s4 from #chuhuo
union all
select dealereturnid DealereturnGoodsID,goods_no,colorid,chima='s5',sl=s5 from #chuhuo
union all
select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s6',sl=s6 from #chuhuo
union all
select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s7',sl=s7 from #chuhuo
union all
select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s8',sl=s8 from #chuhuo
union all
select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s9',sl=s9 from #chuhuo
union all
select dealereturnid DealereturnGoodsID,goods_no,colorid,chima='s10',sl=s10 from #chuhuo
union all
select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s11',sl=s11 from #chuhuo
union all
select dealereturnid,DealereturnGoodsID,goods_no,colorid,chima='s12',sl=s12 from #chuhuo ) torder by dealereturnid ,DealereturnGoodsID,goods_no,colorid,chima报错信息如下:
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。单独一条一条执行语句没有错
用UNPIVOT会方便些,不用写那么多代码,你可以了解下UNPIVOT的用法