Answered by:
dynamic pivot

-
Here is my intial table t1 and final table t2
based on the parameter passed i need to pivot the table
t1
RECORDER_ID CHANNEL_NO M DATE value A 1 KW 11/13/2012 0:00 1072.8 A 2 KVAR 11/13/2012 0:00 447.12 A 1 KW 11/13/2012 0:45 1066.32 A 2 KVAR 11/13/2012 0:45 403.2 A 1 KW 11/13/2012 1:00 1084.32 A 2 KVAR 11/13/2012 1:00 419.04 b 1 KW 11/13/2012 0:00 1167.84 b 2 KVAR 11/13/2012 0:00 444.24 b 3 KW 11/13/2012 0:00 1159.92 b 4 KVAR 11/13/2012 0:00 436.32 t2
recorder_id DATE CHANNEL_1(kw) CHANNEL_2(KVAR) a 11/13/2012 0:00 1072.8 447.12 11/13/2012 0:00 1066.32 403.2 11/13/2012 0:45 1084.32 1084.32 if b is passed DATE CHANNEL_1(kw) CHANNEL_2(KVAR) channel_3(kw) ch_4(kvar) 11/13/2012 0:00 1167 444 1159 436 CHANNEL NO MUST BE DISPLAYED BASED ON RECORDERS ID AND THEN PIVOTED BASED ON THE SELECTION MADE BY CUSTOMER
Question
Answers
-
check this https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
- Marked as answer by rama rose Friday, September 13, 2013 4:03 PM
-
Hi,
Try this link for an example on dynamic pivoting - http://www.allaboutmssql.com/2012/10/sql-server-dynamic-pivoting.html
sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.
- Marked as answer by rama rose Friday, September 13, 2013 4:03 PM
-
You can also create a dynamic pivot from the below query.
DECLARE @TEMP TABLE(RECORDER_ID CHAR(1),CHANNEL_NO INT,M VARCHAR(10),DATE DATETIME,[value] DECIMAL(10,2)) INSERT INTO @TEMP VALUES('A', 1,'KW','11/13/2012 0:00',1072.8 ) INSERT INTO @TEMP VALUES('A', 2,'KVAR','11/13/2012 0:00',447.12 ) INSERT INTO @TEMP VALUES('A', 1,'KW','11/13/2012 0:45',1066.32 ) INSERT INTO @TEMP VALUES('A', 2,'KVAR','11/13/2012 0:45',403.2 ) INSERT INTO @TEMP VALUES('A', 1,'KW','11/13/2012 1:00',1084.32 ) INSERT INTO @TEMP VALUES('A', 2,'KVAR','11/13/2012 1:00',419.04 ) INSERT INTO @TEMP VALUES('b', 1,'KW','11/13/2012 0:00',1167.84 ) INSERT INTO @TEMP VALUES('b', 2,'KVAR','11/13/2012 0:00',444.24 ) INSERT INTO @TEMP VALUES('b', 3,'KW','11/13/2012 0:00',1159.92 ) INSERT INTO @TEMP VALUES('b', 4,'KVAR','11/13/2012 0:00',436.32 ) ;WITH CTE AS (SELECT *,'CHANNEL_' + CAST(CHANNEL_NO AS VARCHAR(1)) + '(' + M + ')' CHANNEL_M FROM @TEMP) ,CTE1 AS ( SELECT RECORDER_ID,[DATE],[CHANNEL_1(KW)],[CHANNEL_2(KVAR)],[CHANNEL_3(KVAR)],[CHANNEL_4(KVAR)] FROM CTE PIVOT (MAX([VALUE]) FOR CHANNEL_M IN ([CHANNEL_1(KW)],[CHANNEL_2(KVAR)],[CHANNEL_3(KVAR)],[CHANNEL_4(KVAR)]) ) PVT ) SELECT RECORDER_ID,[DATE], MAX([CHANNEL_1(KW)]) [CHANNEL_1(KW)], MAX([CHANNEL_2(KVAR)]) [CHANNEL_2(KVAR)], MAX([CHANNEL_3(KVAR)]) [CHANNEL_3(KVAR)], MAX([CHANNEL_4(KVAR)]) [CHANNEL_4(KVAR)] FROM CTE1 GROUP BY RECORDER_ID,[DATE] ORDER BY RECORDER_ID
Regards, RSingh
- Proposed as answer by Allen Li - MSFTModerator Friday, September 13, 2013 6:16 AM
- Marked as answer by rama rose Friday, September 13, 2013 4:03 PM
All replies
-
check this https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
- Marked as answer by rama rose Friday, September 13, 2013 4:03 PM
-
Hi,
Try this link for an example on dynamic pivoting - http://www.allaboutmssql.com/2012/10/sql-server-dynamic-pivoting.html
sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.
- Marked as answer by rama rose Friday, September 13, 2013 4:03 PM
-
You can also create a dynamic pivot from the below query.
DECLARE @TEMP TABLE(RECORDER_ID CHAR(1),CHANNEL_NO INT,M VARCHAR(10),DATE DATETIME,[value] DECIMAL(10,2)) INSERT INTO @TEMP VALUES('A', 1,'KW','11/13/2012 0:00',1072.8 ) INSERT INTO @TEMP VALUES('A', 2,'KVAR','11/13/2012 0:00',447.12 ) INSERT INTO @TEMP VALUES('A', 1,'KW','11/13/2012 0:45',1066.32 ) INSERT INTO @TEMP VALUES('A', 2,'KVAR','11/13/2012 0:45',403.2 ) INSERT INTO @TEMP VALUES('A', 1,'KW','11/13/2012 1:00',1084.32 ) INSERT INTO @TEMP VALUES('A', 2,'KVAR','11/13/2012 1:00',419.04 ) INSERT INTO @TEMP VALUES('b', 1,'KW','11/13/2012 0:00',1167.84 ) INSERT INTO @TEMP VALUES('b', 2,'KVAR','11/13/2012 0:00',444.24 ) INSERT INTO @TEMP VALUES('b', 3,'KW','11/13/2012 0:00',1159.92 ) INSERT INTO @TEMP VALUES('b', 4,'KVAR','11/13/2012 0:00',436.32 ) ;WITH CTE AS (SELECT *,'CHANNEL_' + CAST(CHANNEL_NO AS VARCHAR(1)) + '(' + M + ')' CHANNEL_M FROM @TEMP) ,CTE1 AS ( SELECT RECORDER_ID,[DATE],[CHANNEL_1(KW)],[CHANNEL_2(KVAR)],[CHANNEL_3(KVAR)],[CHANNEL_4(KVAR)] FROM CTE PIVOT (MAX([VALUE]) FOR CHANNEL_M IN ([CHANNEL_1(KW)],[CHANNEL_2(KVAR)],[CHANNEL_3(KVAR)],[CHANNEL_4(KVAR)]) ) PVT ) SELECT RECORDER_ID,[DATE], MAX([CHANNEL_1(KW)]) [CHANNEL_1(KW)], MAX([CHANNEL_2(KVAR)]) [CHANNEL_2(KVAR)], MAX([CHANNEL_3(KVAR)]) [CHANNEL_3(KVAR)], MAX([CHANNEL_4(KVAR)]) [CHANNEL_4(KVAR)] FROM CTE1 GROUP BY RECORDER_ID,[DATE] ORDER BY RECORDER_ID
Regards, RSingh
- Proposed as answer by Allen Li - MSFTModerator Friday, September 13, 2013 6:16 AM
- Marked as answer by rama rose Friday, September 13, 2013 4:03 PM
-