locked
Convert rows to columns using SQL RRS feed

  • Question

  • This is an obvious question, but Ive spent ages looking and cant find an answer.

    I have a table with just two columns -

    ID (INT) incrementing from 1+

    Name (VARCHAR), (of which there are no repeats) ie.

    ID NAME

    1 Bob

    2 Dave

    3 Harry

    ...

    for about 30 records in all

    How can I use sql so this is as follows:

    1              2                3

    BOB       DAVE         HARRY

    Thank you

    Wednesday, March 13, 2013 4:44 PM

Answers

  • Try with PIVOT -

    Example -

    DECLARE @tempTable table(Id int,Name VARCHAR(15))
    INSERT INTO @tempTable
    select 1,'BOB' UNION ALL 
    select 2,'DAVE' UNION ALL 
    select 3,'HARRY' 
    SELECT * FROM @tempTable
    SELECT [1]
    ,[2] 
    ,[3]
    FROM 
    (SELECT Id,Name  FROM 
     @tempTable) AS src 
    	PIVOT (MAX (Name) FOR Id IN 
    	([1] ,[2] ,[3])  
    ) AS pvt
    				  


    Narsimha

    • Marked as answer by fpk1980 Wednesday, March 13, 2013 4:56 PM
    • Unmarked as answer by fpk1980 Wednesday, March 13, 2013 5:52 PM
    • Marked as answer by fpk1980 Wednesday, March 13, 2013 7:11 PM
    Wednesday, March 13, 2013 4:51 PM
  • select * from MyTable PIVOT (max([Name]) for ID IN ([1],[2],[3],...,[30])) pvt

    If you don't know the number of rows in advance, you can generate the above dynamically - search 'dynamic pivot' examples in this forum.


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


    My blog

    • Marked as answer by fpk1980 Wednesday, March 13, 2013 4:56 PM
    • Unmarked as answer by fpk1980 Wednesday, March 13, 2013 5:52 PM
    • Marked as answer by fpk1980 Wednesday, March 13, 2013 7:11 PM
    Wednesday, March 13, 2013 4:52 PM
  • fpk, take note of Naomi's answer above.

    You can create a dynamic query to build a select list based on distinct values within a column using the coalesce function.

    Search up Dynamic Pivot. It'll put hair on your chest!

    • Marked as answer by fpk1980 Wednesday, March 13, 2013 7:11 PM
    Wednesday, March 13, 2013 4:57 PM
  • If you can guarantee the exact structure of the pivot result, then simply

    insert into myNewTable ([1],[2],[3],...)

    select [1],[2],[3],...

    from myTable PIVOT (max([Name]) FOR ID IN ([1],[2],...[30])) pvt


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


    My blog

    • Marked as answer by fpk1980 Wednesday, March 13, 2013 6:31 PM
    • Unmarked as answer by fpk1980 Wednesday, March 13, 2013 7:02 PM
    • Marked as answer by fpk1980 Wednesday, March 13, 2013 7:10 PM
    Wednesday, March 13, 2013 6:18 PM

All replies

  • Try with PIVOT -

    Example -

    DECLARE @tempTable table(Id int,Name VARCHAR(15))
    INSERT INTO @tempTable
    select 1,'BOB' UNION ALL 
    select 2,'DAVE' UNION ALL 
    select 3,'HARRY' 
    SELECT * FROM @tempTable
    SELECT [1]
    ,[2] 
    ,[3]
    FROM 
    (SELECT Id,Name  FROM 
     @tempTable) AS src 
    	PIVOT (MAX (Name) FOR Id IN 
    	([1] ,[2] ,[3])  
    ) AS pvt
    				  


    Narsimha

    • Marked as answer by fpk1980 Wednesday, March 13, 2013 4:56 PM
    • Unmarked as answer by fpk1980 Wednesday, March 13, 2013 5:52 PM
    • Marked as answer by fpk1980 Wednesday, March 13, 2013 7:11 PM
    Wednesday, March 13, 2013 4:51 PM
  • select * from MyTable PIVOT (max([Name]) for ID IN ([1],[2],[3],...,[30])) pvt

    If you don't know the number of rows in advance, you can generate the above dynamically - search 'dynamic pivot' examples in this forum.


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


    My blog

    • Marked as answer by fpk1980 Wednesday, March 13, 2013 4:56 PM
    • Unmarked as answer by fpk1980 Wednesday, March 13, 2013 5:52 PM
    • Marked as answer by fpk1980 Wednesday, March 13, 2013 7:11 PM
    Wednesday, March 13, 2013 4:52 PM
  • Thanks Narsimha

    What happens when there are more users, do I have to rewrite the query each time?

    Wednesday, March 13, 2013 4:52 PM
  • Thanks
    Wednesday, March 13, 2013 4:56 PM
  • fpk, take note of Naomi's answer above.

    You can create a dynamic query to build a select list based on distinct values within a column using the coalesce function.

    Search up Dynamic Pivot. It'll put hair on your chest!

    • Marked as answer by fpk1980 Wednesday, March 13, 2013 7:11 PM
    Wednesday, March 13, 2013 4:57 PM
  • THanks everyone, how do I then take get these values into a table (I have prepared a table tblMethRec with 30 columns) for use elsewhere?
    • Edited by fpk1980 Wednesday, March 13, 2013 5:51 PM
    Wednesday, March 13, 2013 5:49 PM
  • If you can guarantee the exact structure of the pivot result, then simply

    insert into myNewTable ([1],[2],[3],...)

    select [1],[2],[3],...

    from myTable PIVOT (max([Name]) FOR ID IN ([1],[2],...[30])) pvt


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


    My blog

    • Marked as answer by fpk1980 Wednesday, March 13, 2013 6:31 PM
    • Unmarked as answer by fpk1980 Wednesday, March 13, 2013 7:02 PM
    • Marked as answer by fpk1980 Wednesday, March 13, 2013 7:10 PM
    Wednesday, March 13, 2013 6:18 PM
  • Thankyou Naomi
    • Marked as answer by fpk1980 Wednesday, March 13, 2013 7:10 PM
    • Unmarked as answer by fpk1980 Wednesday, March 13, 2013 7:10 PM
    Wednesday, March 13, 2013 6:31 PM
  • Im really sorry to be a pain and Im not sure on etiquette - i.e whether I should start a new thread but I have a related question

    I have that query working now thank you. However, the table that I am inserting into actually has 31 columns. THe 31 column is a parent key value which needs to be passed in with the data from a parameter.

    I have declared the parameter, but how do I incorporate this into the INSERT INTO query

    see....

    DECLARE @project AS INT

    insert into myNewTable ([project],[1],[2],[3],...)

    select [1],[2],[3],...

    from myTable PIVOT (max([Name]) FOR ID IN ([1],[2],...[30])) pvt

    How do I insert project?

    Thanks in advance, its really appreciated and sorry if this should be a new thread

    Frank

    Wednesday, March 13, 2013 7:02 PM
  • Its ok - i got it

    DECLARE @project AS INT

    insert into myNewTable ([project],[1],[2],[3],...)

    select @project, [1],[2],[3],...

    from myTable PIVOT (max([Name]) FOR ID IN ([1],[2],...[30])) pvt

    THanks again

    Wednesday, March 13, 2013 7:11 PM
  • Assuming that the @project variable is initialized with the correct value, the INSERT will be just slightly changed, e.g.

    DECLARE @project AS INT
    
    insert into myNewTable ([project],[1],[2],[3],..., ProjectID)
    
    select [1],[2],[3],...[30], @project as ProjectID
    
    from myTable PIVOT (max([Name]) FOR ID IN ([1],[2],...[30])) pvt


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


    My blog

    Wednesday, March 13, 2013 7:12 PM