Dynamically generate a table with UNPIVOT operation


In SQL Server 2005, you can use UNPIVOT to convert your table to a normalized form(table) like:

Table:

CREATE TABLE [dbo].[PW_Table](

[DateInt] [int]

NULL,

[Meter_1] [float]

NULL,

[Meter_2] [float] NULL,

[Meter_3] [float]

NULL,

[Meter_4] [float]

NULL,

[Meter_5] [float] NULL,

[Meter_6] [float]

NULL,

[Meter_7] [float]

NULL,

[myDate] [datetime] NULL

)

ON [PRIMARY]

–Query with UNPIVOT

SELECT   [dateInt], MeterID, ReadingValue FROM 
(SELECT   [dateInt], [Meter_1], [Meter_2], [Meter_3], [Meter_4], [Meter_5], [Meter_6], [Meter_7]
FROM         PW_Table) t1 
UNPIVOT 
(ReadingValue For MeterID
IN ([Meter_1], [Meter_2], [Meter_3], [Meter_4], [Meter_5], [Meter_6], [Meter_7])) as unPvt 
You want this script to be more flexiable to include unknown number of columns. We can use dynamic query to do this:
 
SET
NOCOUNT ON
DECLARE
@T AS TABLE(y nvarchar(20) NOT NULL PRIMARY KEY)

INSERT
INTO @T SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='PW_Table' and Column_Name Like 'Meter%'

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 @y = (SELECT MIN(y) FROM @T WHERE y > @y)

END

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

DECLARE @sql AS nvarchar(MAX)

SET  @sql = N'SELECT dateInt, MeterID, ReadingValue FROM (SELECT dateInt, ' +@cols + N' FROM   PW_Table) as t
UNPIVOT 
(ReadingValue For MeterID
IN (' + @cols + N')) AS unPvt'

EXEC sp_executesql @sql 

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