34,591
社区成员
发帖
与我相关
我的任务
分享行列互转语句
省份 品名 数量
山东 机床 10
湖南 机床 10
江西 钻床 5
结果
品名 总量 山东 湖南 江西
机床 20 10 10 0
钻床 5 0 0 5
山东 机床 10
湖南 机床 10
江西 钻床 5
结果
品名 总量 山东 湖南 江西
机床 20 10 10 0
钻床 5 0 0 5
...全文
请发表友善的回复…
发表回复
andylist 2011-03-11
- 打赏
- 举报
什么情况才用得上这个行列互转呢？
棉花棒棒糖 2011-03-10
- 打赏
- 举报
消息 1056，级别 15，状态 1，第 1 行
选择列表中的元素数目超出了 4096 元素的最大允许值。
选择列表中的元素数目超出了 4096 元素的最大允许值。
Shawn 2011-03-10
- 打赏
- 举报
CREATE TABLE #temp
(
省份 NVARCHAR(10),
品名 NVARCHAR(10),
数量 INT
)
INSERT #temp
SELECT N'山东', N'机床', 10 UNION ALL
SELECT N'湖南', N'机床', 10 UNION ALL
SELECT N'江西', N'钻床', 5
--SQL:
--#1.
SELECT 品名, 总量 = (ISNULL([山东], 0)+ISNULL([湖南], 0)+ISNULL([江西], 0)), [山东]=ISNULL([山东], 0), [湖南]=ISNULL([湖南], 0), [江西]=ISNULL([江西], 0) FROM
#temp a
PIVOT
(MAX(数量) FOR 省份 IN ([山东], [湖南], [江西])) b
/*
机床 20 10 10 0
钻床 5 0 0 5
*/
--#2.如果省份很多,用动态SQL,拼字符串,再执行
DECLARE @sql NVARCHAR(MAX)
set @sql = N'SELECT 品名,'
+ STUFF((SELECT N','+QUOTENAME(省份)+'=ISNULL('+QUOTENAME(省份)+', 0)' FROM #temp FOR XML PATH('')), 1, 1, '')
+ N',总量=' + STUFF((SELECT '+ISNULL('+QUOTENAME(省份)+', 0)' FROM #temp FOR XML PATH('')), 1, 1, '')
+ N' FROM #temp a PIVOT (MAX(数量) FOR 省份 IN ('
+ STUFF((SELECT ','+QUOTENAME(省份) FROM #temp FOR XML PATH('')), 1, 1, '')
+ N')) b'
EXEC(@sql)
/*
品名 山东 湖南 江西 总量
---------- ----------- ----------- ----------- -----------
机床 10 10 0 20
钻床 0 0 5 5
*/
wing7742 2011-03-10
- 打赏
- 举报
[Quote=引用 3 楼 admczy 的回复:]
省份字段列很多的话,也要case when 吗?不太现实啊
[/Quote]
改动态啊!!
参考
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html?33238
省份字段列很多的话,也要case when 吗?不太现实啊
[/Quote]
改动态啊!!
参考
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html?33238
棉花棒棒糖 2011-03-10
- 打赏
- 举报
省份字段列很多的话,也要case when 吗?不太现实啊
快溜 2011-03-10
- 打赏
- 举报
select 品名,sum(数量) 总量,
sum(case when 省份='山东' then 数量 else 0 end)[山东],
sum(case when 省份='湖南' then 数量 else 0 end) [湖南],
sum(case when 省份='江西' then 数量 else 0 end) [江西]
from tb group by 品名
AcHerat 元老 2011-03-10
- 打赏
- 举报
用 pivot 和 unpivot
棉花棒棒糖 2011-03-10
- 打赏
- 举报
云朵的语句在本地数据库上,五分钟不出结果.....
ssp2009 的语句勉强可运行,但省份列太多的话,一个个列出来太麻烦...
....
等高手支招.
ssp2009 的语句勉强可运行,但省份列太多的话,一个个列出来太麻烦...
....
等高手支招.
函麻 2011-03-10
- 打赏
- 举报
declare @sql nvarchar(4000)
set @sql=N'select 品名,sum(数量) as [总量]'
select @sql=@sql+N',sum(case 省份 when N'''+[省份]+''' then 数量 else 0 end ) as ['+[省份]+']'
from 表名 a
where ID=(select top 1 ID from 表名 where 省份=a.省份)
order by ID
set @sql=@sql+' from 表名 group by 省份'
--select @sql
exec(@sql)
LamarChen 2011-03-10
- 打赏
- 举报
又见行列互转。。。。
棉花棒棒糖 2011-03-10
- 打赏
- 举报
仍然提示这个错误
消息 1056，级别 15，状态 1，第 1 行
选择列表中的元素数目超出了 4096 元素的最大允许值。
消息 1056，级别 15，状态 1，第 1 行
选择列表中的元素数目超出了 4096 元素的最大允许值。
gw6328 2011-03-10
- 打赏
- 举报
[Quote=引用 6 楼 admczy 的回复:]
消息 1056，级别 15，状态 1，第 1 行
选择列表中的元素数目超出了 4096 元素的最大允许值。
[/Quote]
DECLARE @sql NVARCHAR(MAX)
set @sql = N'SELECT 品名,'
+ STUFF((SELECT N','+QUOTENAME(省份)+'=ISNULL('+QUOTENAME(省份)+', 0)' FROM #temp FOR XML PATH('')), 1, 1, '')
+ N',总量=' + STUFF((SELECT '+ISNULL('+QUOTENAME(省份)+', 0)' FROM #temp FOR XML PATH('')), 1, 1, '')
+ N' FROM #temp a PIVOT (MAX(数量) FOR 省份 IN ('
+ STUFF((SELECT ','+QUOTENAME(省份) FROM #temp group by 省份 FOR XML PATH('')), 1, 1, '')
+ N')) b'
EXEC(@sql)
少了个group by 省份
消息 1056，级别 15，状态 1，第 1 行
选择列表中的元素数目超出了 4096 元素的最大允许值。
[/Quote]
DECLARE @sql NVARCHAR(MAX)
set @sql = N'SELECT 品名,'
+ STUFF((SELECT N','+QUOTENAME(省份)+'=ISNULL('+QUOTENAME(省份)+', 0)' FROM #temp FOR XML PATH('')), 1, 1, '')
+ N',总量=' + STUFF((SELECT '+ISNULL('+QUOTENAME(省份)+', 0)' FROM #temp FOR XML PATH('')), 1, 1, '')
+ N' FROM #temp a PIVOT (MAX(数量) FOR 省份 IN ('
+ STUFF((SELECT ','+QUOTENAME(省份) FROM #temp group by 省份 FOR XML PATH('')), 1, 1, '')
+ N')) b'
EXEC(@sql)
少了个group by 省份
