34,594
社区成员
发帖
与我相关
我的任务
分享sql 列转行
mrid, Times, MS, LA, CC, RR, N1H, N1B ,X, Y
1 4:00 1 12 5 5 53 12 45 2
结果
mrid column value
1 Times 4:00
1 MS 1
1 LA 12
1 CC 5
1 RR 5
1 N1H 53
1 N1B 12
1 X 45
1 Y 2
用
UNPIVOT 提示,一列INT类型列错误:
类型与 UNPIVOT 列表中指定的其他列的类型冲突
1 4:00 1 12 5 5 53 12 45 2
结果
mrid column value
1 Times 4:00
1 MS 1
1 LA 12
1 CC 5
1 RR 5
1 N1H 53
1 N1B 12
1 X 45
1 Y 2
用
UNPIVOT 提示,一列INT类型列错误:
类型与 UNPIVOT 列表中指定的其他列的类型冲突
...全文
请发表友善的回复…
发表回复
liang145 2011-05-06
- 打赏
- 举报
create table tbA(mrid int, Times int, MS int, LA int, CC int, RR int, N1H int, N1B int,X int, Y int )
insert into tbA
select 1, 4.00, 1, 12, 5, 5, 53, 12, 45, 2 union all
select 2, 5.00, 2, 13, 6, 6, 54, 13, 46, 3 union all
select 3, 6.00, 3, 14, 7, 7, 55, 14, 47, 4
declare @sql as nvarchar(max)
set @sql=''
select @sql=@sql+' union all select mrid,'''+[name]+''' as [column], '+[name]+' as [value] from tbA '
from syscolumns where [name]<>'mrid' and id=object_id('tbA')
set @sql='select * from ('+stuff(@sql,1,10,'')+') as t order by mrid,[column]'
exec(@sql)
kangkzt 2011-05-06
- 打赏
- 举报
正好需要~
yidianqing 2011-05-05
- 打赏
- 举报
正好需要~
xiequan2 2009-08-07
- 打赏
- 举报
[Quote=引用 11 楼 csshan 的回复:]
declare @t table(mrid varchar(20), Times varchar(20), MS varchar(20), LA INT,
CC varchar(20), RR varchar(20), N1H varchar(20), N1B varchar(20),X varchar(20), Y varchar(20))
如果字段类型有INT,将会出错，大家看看.我就是这个问题
[/Quote]
declare @t table(mrid varchar(20), Times varchar(20), MS varchar(20), LA INT,
CC varchar(20), RR varchar(20), N1H varchar(20), N1B varchar(20),X varchar(20), Y varchar(20))
如果字段类型有INT,将会出错，大家看看.我就是这个问题
[/Quote]
declare @t table(mrid varchar(20), Times varchar(20), MS varchar(20), LA INT,
CC varchar(20), RR varchar(20), N1H varchar(20), N1B varchar(20),X varchar(20), Y varchar(20)) --这里la是int
insert into @t
select 1 , ' 4:00' , 1 , 12 , 5 ,5 , 53 ,12, 45 , 2 union all
select 2 , ' 4:00' , 23 , 23 , 5 ,5 , 53 ,45, 89 , 98
select mrid,b [column],a [value] from
(select mrid ,Times, MS, cast(LA as varchar(20)) LA, CC, RR, N1H, N1B ,X, Y from @t ) t
unpivot
(
a for b in(Times, MS, LA, CC, RR, N1H, N1B ,X, Y )
) u
soft_wsx 2009-08-06
- 打赏
- 举报
楼主还在吗？
soft_wsx 2009-08-06
- 打赏
- 举报
if OBJECT_ID('tb') is not null drop table tb
create table tb(mrid int, Times int, MS int, LA int, CC int, RR int, N1H int, N1B int,X int, Y int )
insert into tb
select 1, 4.00, 1, 12, 5, 5, 53, 12, 45, 2
union all select 2, 4.00, 1, 12, 5, 5, 53, 12, 45, 2
union all select 3, 4.00, 1, 12, 5, 5, 53, 12, 45, 2
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [mrid],[Course]='+quotename(Name,N'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from tb'
from syscolumns where ID=object_id('tb') and Name not in('mrid')--排除不转换的列
order by Colid
print @s
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序
--通过系统表dbo.syscolumns查询对应的表，然后将NAME列水平显示,显示不需要转换为行的放入NOT IN（）中，并按ORDER BY 排序
declare @sql nvarchar(4000)
select @sql=ISNULL(@sql+N' union all ',N'')+N'select [名称]=[mrid],[转换列]='+QUOTENAME(name,N'''')+
N',[转换列值]='+QUOTENAME(name)+N' from tb'
from dbo.syscolumns where id=OBJECT_ID('tb') and name not in('mrid')
order by colid
set @sql=N'select * from ('+@sql+N')t order by 名称,转换列'
print @sql
--以下是动态生能的语句
select * from (select [名称]=[mrid],[转换列]='Times',[转换列值]=[Times] from tb
union all select [名称]=[mrid],[转换列]='MS',[转换列值]=[MS] from tb
union all select [名称]=[mrid],[转换列]='LA',[转换列值]=[LA] from tb
union all select [名称]=[mrid],[转换列]='CC',[转换列值]=[CC] from tb
union all select [名称]=[mrid],[转换列]='RR',[转换列值]=[RR] from tb
union all select [名称]=[mrid],[转换列]='N1H',[转换列值]=[N1H] from tb
union all select [名称]=[mrid],[转换列]='N1B',[转换列值]=[N1B] from tb
union all select [名称]=[mrid],[转换列]='X',[转换列值]=[X] from tb
union all select [名称]=[mrid],[转换列]='Y',[转换列值]=[Y] from tb)t order by 名称,转换列
exec(@sql)
/*结果
名称 转换列 转换列值
1 CC 5
1 LA 12
1 MS 1
1 N1B 12
1 N1H 53
1 RR 5
1 Times 4
1 X 45
1 Y 2
2 CC 5
2 LA 12
2 MS 1
2 N1B 12
2 N1H 53
2 RR 5
2 Times 4
2 X 45
2 Y 2
3 CC 5
3 LA 12
3 MS 1
3 N1B 12
3 N1H 53
3 RR 5
3 Times 4
3 X 45
3 Y 2*
*/csshan 2009-08-06
- 打赏
- 举报
declare @t table(mrid varchar(20), Times varchar(20), MS varchar(20), LA INT,
CC varchar(20), RR varchar(20), N1H varchar(20), N1B varchar(20),X varchar(20), Y varchar(20))
如果字段类型有INT,将会出错，大家看看.我就是这个问题
CC varchar(20), RR varchar(20), N1H varchar(20), N1B varchar(20),X varchar(20), Y varchar(20))
如果字段类型有INT,将会出错，大家看看.我就是这个问题
xiequan2 2009-08-06
- 打赏
- 举报
[Quote=引用 9 楼 csshan 的回复:]
mrid 是自增列
[/Quote]
mrid 是自增列
[/Quote]
declare @t table(mrid varchar(20), Times varchar(20), MS varchar(20), LA varchar(20),
CC varchar(20), RR varchar(20), N1H varchar(20), N1B varchar(20),X varchar(20), Y varchar(20))
insert into @t
select 1 , ' 4:00' , 1 , 12 , 5 ,5 , 53 ,12, 45 , 2 union all
select 2 , ' 4:00' , 23 , 23 , 5 ,5 , 53 ,45, 89 , 98
select mrid,b [column],a [value] from @t
unpivot
(
a for b in(Times, MS, LA, CC, RR, N1H, N1B ,X, Y )
) u
/*
mrid column value
-------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------
1 Times 4:00
1 MS 1
1 LA 12
1 CC 5
1 RR 5
1 N1H 53
1 N1B 12
1 X 45
1 Y 2
2 Times 4:00
2 MS 23
2 LA 23
2 CC 5
2 RR 5
2 N1H 53
2 N1B 45
2 X 89
2 Y 98
(18 行受影响)
*/
csshan 2009-08-06
- 打赏
- 举报
mrid 是自增列
soft_wsx 2009-08-06
- 打赏
- 举报
占便宜
/*
Student 数学 物理 英语 语文
李四 77 85 65 65
张三 87 90 82 78
*/
select *
from (select [Student],[Course]='数学',[Score]=[数学] from #a union all
select [Student],[Course]='物理',[Score]=[物理] from #a union all
select [Student],[Course]='英语',[Score]=[英语] from #a union all
select [Student],[Course]='语文',[Score]=[语文] from #a)t
order by [Student],[Course]
/*
Student Course Score
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78
*/
xiequan2 2009-08-06
- 打赏
- 举报
declare @t table(mrid varchar(20), Times varchar(20), MS varchar(20), LA varchar(20),
CC varchar(20), RR varchar(20), N1H varchar(20), N1B varchar(20),X varchar(20), Y varchar(20))
insert into @t select 1 , ' 4:00' , 1 , 12 , 5 ,5 , 53 ,12, 45 , 2
select mrid=1,b [value] ,a [column] from @t
unpivot
(
a for b in(Times,mrid, MS, LA, CC, RR, N1H, N1B ,X, Y )
) u
/*
mrid value column
----------- -------------------------------------------------------------------------------------------------------------------------------- --------------------
1 Times 4:00
1 mrid 1
1 MS 1
1 LA 12
1 CC 5
1 RR 5
1 N1H 53
1 N1B 12
1 X 45
1 Y 2
(10 行受影响)
*/
SQL77 2009-08-06
- 打赏
- 举报
mrid, Times, MS, LA, CC, RR, N1H, N1B ,X, Y
1 4:00 1 12 5 5 53 12 45 2
SELECT
mrid,[column]='Times',[value]=(select Times from tb where mrid=1)
from tb where mrid=1
union all
SELECT
mrid,[column]='MS',[value]=(select MS from tb where mrid=1)
from tb where mrid=1
union all
SELECT
mrid,[column]='la',[value]=(select LA from tb where mrid=1)
from tb where mrid=1
union all
SELECT
mrid,[column]='cc',[value]=(select cc from tb where mrid=1)
from tb where mrid=1
union all
SELECT
mrid,[column]='rr',[value]=(select rr from tb where mrid=1)
from tb where mrid=1
union all
SELECT
mrid,[column]='N1H',[value]=(select N1H from tb where mrid=1)
from tb where mrid=1
union all
SELECT
mrid,[column]='N1B ',[value]=(select N1B from tb where mrid=1)
from tb where mrid=1
union all
SELECT
mrid,[column]='x',[value]=(select x from tb where mrid=1)
from tb where mrid=1
union all
SELECT
mrid,[column]='y',[value]=(select y from tb where mrid=1)
from tb where mrid=1soft_wsx 2009-08-06
- 打赏
- 举报
怎么发的怎么差不多
lihan6415151528 2009-08-06
- 打赏
- 举报
*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果)：
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/
create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go
--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end
--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')
--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t
--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。
--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程 分数
---- ------ ------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
------------------
*/
select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tb
feixianxxx 2009-08-06
- 打赏
- 举报
--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂
整理人：中国风(Roy)
日期:2008.06.06
******************************************************************************************************************************************************/
--1、行互列
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] 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([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('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]
exec('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),
[总成绩]=sum([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、列转行
--> --> (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 行受影响)
*/soft_wsx 2009-08-06
- 打赏
- 举报
不好意思！研究一下先，再告诉你
--小F-- 2009-08-06
- 打赏
- 举报
/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果)：
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/
create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go
--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end
--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')
--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t
--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。
--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程 分数
---- ------ ------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
------------------
*/
select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tb
