--行轉列小實體 --創建測驗表 if object_id(N'test', N'U') is not null drop table test go with PivotTable as ( select 'xxx' as czy, '點贊' as czlx, 2 as num union all select 'xxx', '瀏覽' as czlx, 14 as num union all select 'yyy', '瀏覽' as czlx, 10 as num union all select 'zzz', '瀏覽', 30 union all select 'zzz', '點贊', 3 ) select * into test from PivotTable go --創建存盤程序 if exists(select name from sysobjects where name = 'usp_GetPivotInfo') drop proc usp_GetPivotInfo go create proc usp_GetPivotInfo as declare @czlx varchar(500), @SQL varchar(2000) select @czlx = stuff((select distinct ',[' + czlx + ']' from test for xml path ('')),1,1,'') --select @czlx set @SQL = 'select czy, {#} from test pivot(sum(num) for czlx in ({#})) as T'; set @SQL = replace(@SQL, '{#}', @czlx); exec(@SQL); go exec usp_GetPivotInfo ;
交叉前
交叉后
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/4133.html
標籤：SQL Server
上一篇：SQL列的資料型別分類
下一篇：一個字串轉數字的小功能