none
dynamic pivot

    Question

  • 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

    Thursday, September 12, 2013 1:30 AM

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
    Thursday, September 12, 2013 3:02 AM
  • 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
    Thursday, September 12, 2013 3:11 AM
  • 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

    Thursday, September 12, 2013 3:23 AM

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
    Thursday, September 12, 2013 3:02 AM
  • 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
    Thursday, September 12, 2013 3:11 AM
  • 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

    Thursday, September 12, 2013 3:23 AM
  • Thank you 

      I used dynamic pivoting to solve the problem.

    Thank you all for the help.

    Friday, September 13, 2013 4:04 PM