22,210
社区成员
发帖
与我相关
我的任务
分享
declare @s varchar(8000),@s2 varchar(8000)
select @s = isnull(@s+',','') + '['+ltrim(id)+']= max('''+[name]+''')'
,@s2=isnull(@s2+',','') + 'max('''+ltrim([count])+''') '
from ta
exec('select '+@s + ',sum(count) as s_count from ta union all select '+@s2+',null')
/*
1 2 3 4 5 s_count
---- ---- ---- ---- ---- -----------
衣服 裤子 鞋子 帽子 链子 830
100 300 200 120 110 NULL
*/
select [衣服],[裤子],[鞋子],[帽子],[链子]
from ( select name,count from ta ) p
pivot ( max(count) for name in([衣服],[裤子],[鞋子],[帽子],[链子])) as pvt
衣服 裤子 鞋子 帽子 链子
----------- ----------- ----------- ----------- -----------
100 300 200 120 110
(1 行受影响)
select [衣服],[裤子],[鞋子],[帽子],[链子]
from ( select name,count from ta ) p
pivot ( max(count) for name in([衣服],[裤子],[鞋子],[帽子],[链子])) as pvt
------------------------------------
-- Author: happyflsytone
-- Date:2008-09-28 00:00:27
------------------------------------
-- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(id INT,name NVARCHAR(2),count INT)
Go
INSERT INTO ta
SELECT 1,'衣服',100 UNION ALL
SELECT 2,'裤子',300 UNION ALL
SELECT 3,'鞋子',200 UNION ALL
SELECT 4,'帽子',120 UNION ALL
SELECT 5,'链子',110
GO
--Start
declare @s varchar(8000)
select @s = isnull(@s+',','') + '['+name+']= max(case when name = '''+name+''' then count else 0 end)'
from ta
exec('select '+@s + ',sum(count) as s_count from ta')
--Result:
/*
衣服 裤子 鞋子 帽子 链子 s_count
----------- ----------- ----------- ----------- ----------- -----------
100 300 200 120 110 830
(1 行受影响)
*/
--End
declare
@str varchar(1000),
@str2 varchar(1000)
set @str = 'select '
set @str2 = ''
select @str2 = @str2+','+'max(case name when '''+name+''' then count else 0 end)as'''+name+''''
from(select distinct name from tb)a
exec(@str + @str2 + 'sum(count)as''zs'' from tb')