34,590
社区成员
发帖
与我相关
我的任务
分享
--執行查詢
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = ISNULL(@sql , '') +'[' + name + '],' FROM place AS a
SET @sql = 'SELECT * FROM main PIVOT(SUM(weight1) FOR place IN ('+ LEFT(@sql , LEN(@sql) - 1)+ ')) a'
PRINT @sql
EXEC(@sql)
IF EXISTS(
SELECT NAME
FROM sys.objects AS o
WHERE NAME = 'test'
)
DROP TABLE test
GO
CREATE TABLE test
(
goods NVARCHAR(03),
place NVARCHAR(02),
WEIGHT1 INT
)
GO
INSERT INTO test
SELECT N'黃沙',
N'北京',
100 UNION ALL
SELECT N'水泥',
N'天津',
200 UNION ALL
SELECT N'玻璃',
N'上海',
300
--執行查詢
SELECT *
FROM test PIVOT(SUM(weight1) FOR place IN ([北京], [天津], [上海])) a
/*結果
goods 北京 天津 上海
----- ----------- ----------- -----------
水泥 NULL 200 NULL
玻璃 NULL NULL 300
黃沙 100 NULL NULL
(3 row(s) affected)
*/
select * form main pivot(sum(weight) for place in ([北京],[ 天津],[上海])) as a
declare @sql nvarchar(max)
select @sql=isnull(@sql,'')+',sum(case when place='''+rtrim(name)+''' then weight else 0 end)['+rtrim(name)+']' from place
set @sql='select m.goods'+@sql+' from goods as g left join main as m on m.goods=g.name group by m.goods,g.id order by g.id'
execute(@sql)
use test
create table main(goods varchar(30),place varchar(50),weight int)
insert into main
select '黃沙' , '北京', 100 union all
select '水泥' , '天津', 200 union all
select '玻璃' , '上海', 300
declare @sql varchar(max)
select @sql=isnull(@sql+',','')+quotename(place,'[') from main
group by place
set @sql='select * from main pivot (max(weight) for place in('+@sql+'))p'
--print @sql
exec (@sql)