locked
Turn rows to columns RRS feed

  • Question

  • I've looked at Pivots and Cross tabs but just can't find anything to provide the following:

    Current table

    FullName rtb1a rtb1b rtb1c rtb1d rtb1e
    David Prowse 1 0 0 0 0
    Carrie Fischer 1 1 1 1 0
    Mark Hamell 0 1 1 1 1
    Anthony Danials 1 1 0 1 1

    Desired Output

    Title David Prowse Carrie Fischer Mark Hamell Anthony Danials
    rtb1a 1 1 0 1
    rtb1b 0 1 1 1
    rtb1c 0 1 1 0
    rtb1d 0 1 1 1
    rtb1e 0 0 1 1

    Any help much appreciated.


    Allan Browning

    Friday, April 5, 2013 6:10 AM

Answers

  • CREATE TABLE CurrentTable (id       INT IDENTITY(1, 1),  FullName VARCHAR(50), rtb1a    INT,  rtb1b    INT,    rtb1c    INT,   rtb1d    INT,   rtb1e    INT)
    
    INSERT INTO CurrentTable
    VALUES      ('David Prowse',1,0,0,0,0),
                ('Carrie Fischer',1,1,1,1,0),
                ('Mark Hamell',0,1,1,1,1),
                ('Anthony Danials',1,1,0,1,1) 
    
    --UNPIVOT with VALUES constructor and PIVOT
    ;WITH mycte
         AS (SELECT *
             FROM   CurrentTable
                    CROSS Apply (VALUES ( rtb1a,'rtb1a'),
                                        ( rtb1b,'rtb1b'),
                                        ( rtb1c,'rtb1c'),
                                        ( rtb1d,'rtb1d'),
                                        ( rtb1e,'rtb1e')) d(rtb1, title))
    SELECT title,[David Prowse],[Carrie Fischer],[Mark Hamell],[Anthony Danials]
    FROM   (SELECT FullName,rtb1,title
            FROM   mycte) AS src
           PIVOT (MAX(rtb1)
                 FOR FullName IN ([David Prowse],[Carrie Fischer],[Mark Hamell],[Anthony Danials])) pvt 
    
    
    
    --Standard SQL with CASE expression 
    
    
    SELECT title, Max(CASE WHEN P.FullName = 'David Prowse' THEN rtb1a END) AS 'David Prowse',
      Max(CASE WHEN P.FullName ='Carrie Fischer' THEN rtb1a END) AS 'Carrie Fischer',
      Max(CASE WHEN P.FullName = 'Mark Hamell' THEN rtb1a END) AS 'Mark Hamell',
      Max(CASE WHEN P.FullName = 'Anthony Danials' THEN rtb1a END) AS 'Anthony Danials'
    FROM
     (SELECT 'rtb1a' as title,FullName, rtb1a FROM CurrentTable
      UNION All
      SELECT 'rtb1b', FullName, rtb1b FROM CurrentTable
      UNION All
      SELECT 'rtb1c',FullName, rtb1c FROM CurrentTable 
        UNION All
      SELECT 'rtb1d',FullName, rtb1d FROM CurrentTable 
        UNION All
      SELECT 'rtb1e',FullName, rtb1e FROM CurrentTable 
      
      ) P
    GROUP BY title
    ORDER BY title
    
    
    drop table CurrentTable
    /*
    
    --Result
    
    title	David Prowse	Carrie Fischer	Mark Hamell	Anthony Danials
    rtb1a	1	1	0	1
    rtb1b	0	1	1	1
    rtb1c	0	1	1	0
    rtb1d	0	1	1	1
    rtb1e	0	0	1	1
    */

    • Marked as answer by visionquestit Friday, April 5, 2013 8:04 PM
    Friday, April 5, 2013 3:26 PM

All replies

  • You're right with pivots. The problem is that your first representation is already not normalized, thus a normal pivot does not help. Use UNPIVOT first to create a normalized representation of your data before using PIVOT.
    Friday, April 5, 2013 6:14 AM
  • This example shows how an unpivot works. Please do check it out.

    IF OBJECT_ID('tempdb..#UnPivotSampleTable') IS NOT NULL
    DROP TABLE #UnPivotSampleTable
    
    CREATE TABLE #UnPivotSampleTable
    (
    Customer VARCHAR(15)
    ,January INT
    ,February INT
    )
    GO
    
    INSERT INTO #UnPivotSampleTable
    SELECT 'Ritesh',1,6 UNION ALL
    SELECT 'Rajan',NULL,8 UNION ALL
    SELECT 'Teerth',3,NULL
    GO
    
    SELECT * FROM #UnPivotSampleTable
    GO
    
    
    SELECT *
    
    FROM
    
    (
    
    SELECT Customer,Orders
    
    FROM #UnPivotSampleTable
    
    UNPIVOT
    
    (
    
    VALUE
    
    FOR Orders in (January,February)
    
    ) UnPivo
    
    ) tab
    
    GO
    
    


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Friday, April 5, 2013 11:40 AM
  • CREATE TABLE CurrentTable (id       INT IDENTITY(1, 1),  FullName VARCHAR(50), rtb1a    INT,  rtb1b    INT,    rtb1c    INT,   rtb1d    INT,   rtb1e    INT)
    
    INSERT INTO CurrentTable
    VALUES      ('David Prowse',1,0,0,0,0),
                ('Carrie Fischer',1,1,1,1,0),
                ('Mark Hamell',0,1,1,1,1),
                ('Anthony Danials',1,1,0,1,1) 
    
    --UNPIVOT with VALUES constructor and PIVOT
    ;WITH mycte
         AS (SELECT *
             FROM   CurrentTable
                    CROSS Apply (VALUES ( rtb1a,'rtb1a'),
                                        ( rtb1b,'rtb1b'),
                                        ( rtb1c,'rtb1c'),
                                        ( rtb1d,'rtb1d'),
                                        ( rtb1e,'rtb1e')) d(rtb1, title))
    SELECT title,[David Prowse],[Carrie Fischer],[Mark Hamell],[Anthony Danials]
    FROM   (SELECT FullName,rtb1,title
            FROM   mycte) AS src
           PIVOT (MAX(rtb1)
                 FOR FullName IN ([David Prowse],[Carrie Fischer],[Mark Hamell],[Anthony Danials])) pvt 
    
    
    
    --Standard SQL with CASE expression 
    
    
    SELECT title, Max(CASE WHEN P.FullName = 'David Prowse' THEN rtb1a END) AS 'David Prowse',
      Max(CASE WHEN P.FullName ='Carrie Fischer' THEN rtb1a END) AS 'Carrie Fischer',
      Max(CASE WHEN P.FullName = 'Mark Hamell' THEN rtb1a END) AS 'Mark Hamell',
      Max(CASE WHEN P.FullName = 'Anthony Danials' THEN rtb1a END) AS 'Anthony Danials'
    FROM
     (SELECT 'rtb1a' as title,FullName, rtb1a FROM CurrentTable
      UNION All
      SELECT 'rtb1b', FullName, rtb1b FROM CurrentTable
      UNION All
      SELECT 'rtb1c',FullName, rtb1c FROM CurrentTable 
        UNION All
      SELECT 'rtb1d',FullName, rtb1d FROM CurrentTable 
        UNION All
      SELECT 'rtb1e',FullName, rtb1e FROM CurrentTable 
      
      ) P
    GROUP BY title
    ORDER BY title
    
    
    drop table CurrentTable
    /*
    
    --Result
    
    title	David Prowse	Carrie Fischer	Mark Hamell	Anthony Danials
    rtb1a	1	1	0	1
    rtb1b	0	1	1	1
    rtb1c	0	1	1	0
    rtb1d	0	1	1	1
    rtb1e	0	0	1	1
    */

    • Marked as answer by visionquestit Friday, April 5, 2013 8:04 PM
    Friday, April 5, 2013 3:26 PM
  • Thank you!!!!!!!

    Allan Browning

    Friday, April 5, 2013 8:04 PM