locked
Is it possible through pivot ? RRS feed

  • Question

  • I have a table like this 

    Table

    and i want output like

    output

    Is it possible using pivot or not ? Can you please help me? If not pivot, how can i avhieve it?

    I have provided create query and insert query for your ease.

    CREATE TABLE table1(col1 INT,col2 INT,col3 INT,col4 INT,col5 INT,col6 INT,col7 char(10));

    INSERT INTO dbo.table1 (col1, col2, col3, col4, col5, col6, col7)
    VALUES (101, 12, 14, 15, 16, 17, 'A')
    GO

    INSERT INTO dbo.table1 (col1, col2, col3, col4, col5, col6, col7)
    VALUES (102, 25, 26, 27, 16, 17, 'A')
    GO

    INSERT INTO dbo.table1 (col1, col2, col3, col4, col5, col6, col7)
    VALUES (101, 25, 25, 24, 14, 17, 'B')
    GO

    Friday, June 28, 2013 2:25 AM

Answers

  • Try

    ;
    
    WITH cte
    AS (
    	SELECT col1
    		,[Value]
    		,Col7
    		,ColName
    	FROM dbo.table1
    	UNPIVOT([Value] FOR ColName IN (
    				[col2]
    				,[col3]
    				,[col4]
    				,[col5]
    				,[col6]
    				)) unpvt
    	)
    SELECT col1 AS myCol1
    	,COALESCE([A], 0) AS myCol2
    	,COALESCE([B], 0) AS myCol3
    FROM cte
    PIVOT(min([Value]) FOR Col7 IN (
    			[A]
    			,[B]
    			)) pvt
    ORDER BY myCol1
    	,myCol2;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by balakrishna141 Friday, June 28, 2013 5:26 AM
    • Marked as answer by Yuvraj Rimal Monday, July 1, 2013 8:45 AM
    Friday, June 28, 2013 2:46 AM

All replies

  • Try

    ;
    
    WITH cte
    AS (
    	SELECT col1
    		,[Value]
    		,Col7
    		,ColName
    	FROM dbo.table1
    	UNPIVOT([Value] FOR ColName IN (
    				[col2]
    				,[col3]
    				,[col4]
    				,[col5]
    				,[col6]
    				)) unpvt
    	)
    SELECT col1 AS myCol1
    	,COALESCE([A], 0) AS myCol2
    	,COALESCE([B], 0) AS myCol3
    FROM cte
    PIVOT(min([Value]) FOR Col7 IN (
    			[A]
    			,[B]
    			)) pvt
    ORDER BY myCol1
    	,myCol2;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by balakrishna141 Friday, June 28, 2013 5:26 AM
    • Marked as answer by Yuvraj Rimal Monday, July 1, 2013 8:45 AM
    Friday, June 28, 2013 2:46 AM
  • It worked for me, Thanks a lot Naomi
    Monday, July 1, 2013 8:46 AM