locked
Column values to be converted as Column names RRS feed

  • Question

  • I have a table called Name 

    CREATE TABLE Name(
    [Name] [varchar](10) NULL


    INSERT INTO Name VALUES ('XXX')
    INSERT INTO Name VALUES ('PPP')
    INSERT INTO Name VALUES ('MNO')
    INSERT INTO Name VALUES ('PQR')


    i have a requirement where the values of column(Name)to be converted as  Column name  as shown below.


    can anybody please  help me .


    Wednesday, March 11, 2015 7:27 AM

Answers

  • Hope below queries help you:

    -- Option 1
    SELECT 
    	MAX(CASE WHEN Name = 'XXX' THEN Name END) AS 'XXX' ,
    	MAX(CASE WHEN Name = 'PPP' THEN Name END) AS 'PPP' ,
    	MAX(CASE WHEN Name = 'MNO' THEN Name END) AS 'MNO' ,
    	MAX(CASE WHEN Name = 'PQR' THEN Name END) AS 'PQR' 
    FROM Name
    -- Option 2
    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)
    select @cols = STUFF((SELECT ',' + QUOTENAME(Name) 
                        from Name
                        group by Name
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    set @query = 'SELECT ' + @cols + ' from 
                 (
                    select Name
                    from Name
                ) x
                pivot 
                (
                    max(Name)
                    for Name in (' + @cols + ')
                ) p '
    execute(@query)


    -Vaibhav Chaudhari

    • Proposed as answer by Eric__Zhang Thursday, March 12, 2015 6:42 AM
    • Marked as answer by mirandaroopa Friday, March 13, 2015 6:10 AM
    Wednesday, March 11, 2015 8:05 AM
  • Did you check this link

    http://stackoverflow.com/questions/15042663/dynamic-sql-server-pivot-table

    Also Vaibhav has given in option 2 same thing.

    • Proposed as answer by Eric__Zhang Thursday, March 12, 2015 6:43 AM
    • Marked as answer by Eric__Zhang Wednesday, March 18, 2015 5:36 AM
    Wednesday, March 11, 2015 9:05 AM

All replies

  • Select *
    From
        (
            Select name 
            From name          
        ) As a 
    Pivot (count(name) For name In ([XXX], [PPP], [MNO], [PQR])) As A

    https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/

    http://stackoverflow.com/questions/15042663/dynamic-sql-server-pivot-table

    Wednesday, March 11, 2015 8:01 AM
  • Hope below queries help you:

    -- Option 1
    SELECT 
    	MAX(CASE WHEN Name = 'XXX' THEN Name END) AS 'XXX' ,
    	MAX(CASE WHEN Name = 'PPP' THEN Name END) AS 'PPP' ,
    	MAX(CASE WHEN Name = 'MNO' THEN Name END) AS 'MNO' ,
    	MAX(CASE WHEN Name = 'PQR' THEN Name END) AS 'PQR' 
    FROM Name
    -- Option 2
    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)
    select @cols = STUFF((SELECT ',' + QUOTENAME(Name) 
                        from Name
                        group by Name
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    set @query = 'SELECT ' + @cols + ' from 
                 (
                    select Name
                    from Name
                ) x
                pivot 
                (
                    max(Name)
                    for Name in (' + @cols + ')
                ) p '
    execute(@query)


    -Vaibhav Chaudhari

    • Proposed as answer by Eric__Zhang Thursday, March 12, 2015 6:42 AM
    • Marked as answer by mirandaroopa Friday, March 13, 2015 6:10 AM
    Wednesday, March 11, 2015 8:05 AM
  • You need to transpose the values these to column names.

    This can be done by using PIVOT operator, check this blog post for its use and how to do reverse (UNPIVOT): http://sqlwithmanoj.com/2009/04/12/ms-sql-server-2005-new-feature-pivot-and-unpivot/


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    Wednesday, March 11, 2015 8:13 AM
  • Hi ,

    Thank you but  i dont want to hard code the values ie  In ([XXX], [PPP], [MNO], [PQR])

    is there any other way ?

    Wednesday, March 11, 2015 9:00 AM
  • Did you check this link

    http://stackoverflow.com/questions/15042663/dynamic-sql-server-pivot-table

    Also Vaibhav has given in option 2 same thing.

    • Proposed as answer by Eric__Zhang Thursday, March 12, 2015 6:43 AM
    • Marked as answer by Eric__Zhang Wednesday, March 18, 2015 5:36 AM
    Wednesday, March 11, 2015 9:05 AM
  • If you don't want to hard code, the you would need to create dynamic PIVOT script.

    It would read all your row values and transpose those to several columns. Check a solution here.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page


    Wednesday, March 11, 2015 9:06 AM
  • Thank you Vaibhav Chaudhari its working 
    Friday, March 13, 2015 6:11 AM