09
SQL Server 動態 Pivot 欄位
網路上找到的，趕緊記錄下來，可惜就是stored procedure，要不然可用性更大…
— =============================================
— Author:
— Create date: <Create Date,,>
— Description: –SQLTable(Table或是T-SQL)
–Pivot_Org_Column(要呈現那些原本的欄位名稱)
–Pivot_Use_Expression(轉置欄位要用的運算式,example:sum,max…etc)
–Pivot_Value_Column(需要運算的欄位名稱)
–Pivot_Trans_Column(需轉置欄位,only column)
— =============================================
CREATE PROCEDURE [dbo].[OT_Pivot_sp](
@SQLTable varchar(max),
@Pivot_Org_Column varchar(5000),
@Pivot_Use_Expression varchar(10),
@Pivot_Value_Column varchar(5000),
@Pivot_Trans_Column varchar(5000))
AS
BEGIN
declare @columns varchar(max)
declare @sql nvarchar(max)
set @sql = N’set @columns = substring((select ”, [”+convert(varchar,’+@Pivot_Trans_Column+’)+”]” from ‘+@SQLTable +’ group by ‘+@Pivot_Trans_Column+’ order by 1 for xml path(””)),2,8000)’
execute sp_executesql @sql,
N’@columns varchar(max) output’,
@columns=@columns output
set @sql = N’SELECT * FROM
(SELECT ‘+@Pivot_Org_Column+’,’+@Pivot_Trans_Column+’,’+@Pivot_Value_Column+’ from ‘+@SQLTable+’) src
PIVOT
(‘+@Pivot_Use_Expression+'(‘+@Pivot_Value_Column+’) FOR ‘+@Pivot_Trans_Column+’ IN (‘+@columns+’) ) pvt
ORDER BY 1′
execute sp_executesql @sql
END
There are no comments.