Dynamic PIVOT in SQL Server 2005


Here is a sample with synamic script for pivot:

—-create Table yourTable4 (Row int, ID int, Poltype int)

–INSERT INTO yourTable4

–SELECT 1,370,103

–UNION ALL SELECT 2,370,100

–UNION ALL SELECT 3,370,381

–UNION ALL SELECT 4,370,580

–UNION ALL SELECT 1,380,384

–UNION ALL SELECT 2,380,181

–UNION ALL SELECT 3,380,384

–UNION ALL SELECT 4,380,181

—-SELECT ID, [1] as Poltype1,[2] as Poltype1,[3] as Poltype3 FROM

—-(

—-SELECT ID, ROW, Poltype FROM yourTable4

—-

—-) src

—-PIVOT (MAX(Poltype) FOR ROW IN ([1],[2],[3])) pvt

 

 

 

SET NOCOUNT ON

DECLARE @T AS TABLE(y int NOT NULL PRIMARY KEY)

INSERT

INTO @T SELECT DISTINCT ROW FROM yourTable4

DECLARE

@T1 AS TABLE(num int NOT NULL PRIMARY KEY)

DECLARE

@i AS int

SET

@i=1

WHILE

@i <20

BEGIN

INSERT

INTO @T1 SELECT @i

SET

@i=@i+1

END

DECLARE

@cols AS nvarchar(MAX), @cols2 AS nvarchar(MAX),@y AS nvarchar(20)

SET

@y = (SELECT MIN(y) FROM @T)

SET

@cols = N

SET

@cols2 = N

WHILE

@y IS NOT NULL

BEGIN

SET

@cols = @cols + N‘,[‘+CAST(@y AS nvarchar(20))+N‘]’

SET

@cols2 = @cols2 + N‘, [‘+CAST(@y AS nvarchar(20))+N‘] AS [Poltype’+CAST(@y AS nvarchar(20))+N‘]’

SET

@y = (SELECT MIN(y) FROM @T WHERE y > @y)

END

SET

@cols = SUBSTRING(@cols, 2, LEN(@cols))

SET

@cols2 = SUBSTRING(@cols2, 2, LEN(@cols2))

–print @cols2

DECLARE

@sql AS nvarchar(MAX)

SET

@sql = N‘SELECT ID, ‘ +@cols2 + N‘ FROM ( SELECT ID, ROW, Poltype FROM yourTable4) src

PIVOT (MAX(Poltype) FOR ROW IN(‘

+ @cols + N‘)) AS pvt’ 

EXEC sp_executesql @sql

 

–Another one put into a stored procedure:(http://forums.asp.net/t/1345606.aspx)

Alter Procedure mySP_DynamicPivotSample

@myYear int

 

AS

SET

NOCOUNT ON

BEGIN

DECLARE

@T AS TABLE(y int NOT NULL PRIMARY KEY)

;

WITH mycte

AS

(

SELECT

1 as m, DATEADD(month,0,CAST(‘1/1/’+CAST(@myYear as CHAR(4)) as datetime)) as dt1

UNION

ALL

SELECT

m+1, DATEADD(month,1,dt1) as dt1 FROM mycte WHERE m<24

)

INSERT

INTO @T SELECT RIGHT(‘0’+ CAST(Month(dt1)as varchar(2)),2)+

CAST

(Year(dt1)as char(4)) as mymonth FROM mycte

–SELECT * FROM @T

DECLARE @cols AS nvarchar(MAX), @cols2 AS nvarchar(MAX),@y AS int

SET

@y = (SELECT MIN(y) FROM @T)

SET

@cols = N”

SET

@cols2 = N”

WHILE

@y IS NOT NULL

BEGIN

SET @cols = @cols + N’,[‘+RIGHT(‘0’+CAST(@y AS nvarchar(10)),6)+N’]’

 

SET @cols2 = @cols2 + N’,ISNULL([‘+RIGHT(‘0’+CAST(@y AS nvarchar(10)),6)+N’],0) as [‘+RIGHT(‘0’+CAST(@y AS nvarchar(10)),6)+N’]’

SET @y = (SELECT MIN(y) FROM @T WHERE y > @y)

END

SET

@cols = SUBSTRING(@cols, 2, LEN(@cols))

SET

@cols2 = SUBSTRING(@cols2, 2, LEN(@cols2))

–print @cols2

DECLARE

@sql AS nvarchar(MAX)

SET @sql = N’SELECT Descrizione, Ordine, Tipo,’ + @cols2 + N’

FROM (SELECT Descrizione, Ordine, Tipo, Importo,

RIGHT(”0”+CONVERT(varchar(2),[Mese]),2) +CONVERT(char(4),[Anno]) as MonthYear

FROM Flussi) AS F

PIVOT ( MAX(Importo) FOR MonthYear IN(‘

+ @cols + N’)) AS P’

EXEC

sp_executesql @sql

End

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s