none
SQL convert multiple columns into rows

    Question

  • I have data in multiple columns that I would like to get into grouped rows.  Example of data format (This is one row): ID1, ID1Data1, ID1Data2, ID2, ID2Data1, ID2Data2.  There are a fixed number of IDs but it needs to be flexable to add more.
    I would like the data to be like this (Two rows):
    ID1, ID1Data1, ID1Data2
    ID2, ID2Data1, ID2Data2

    How can I convert this?  I have looked at the unpivot but just can't get it to work how I need it.  I know I could use a union but I have a bunch of IDs.

    Thanks,

    Josh

    Friday, June 04, 2010 8:29 PM

Answers

  • If the number of same columns is always known in advance (3 in this case) try this solution:

     

    create table TestIDS 
    (ID1 int, ID1Data1 int, ID1Data2 int, 
    ID2 int, ID2Data1 int, ID2Data2 int, 
    ID3 int, ID3Data1 int, ID3Data2 int,
    ID4 int, ID4Data1 int, ID4Data2 int)
    insert into TestIDS values (1,2,3,
    2,5,6,
    3,7,8,
    4,10,12)
    
    go
    
    declare @SQL nvarchar(max), @Total_Columns int
    select @Total_Columns = COUNT(1) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TestIDs'
     set @SQL = ' SELECT '
    select @SQL = @SQL + 
    
    Column_Name + 
     Case when Ordinal_Position = 1 then ' AS ID'
      when Ordinal_Position = 2 then ' AS Data1'
      when Ordinal_Position = 3 then ' AS Data2'
     else '' end +  
     Case when Ordinal_Position = @Total_Columns then '
      FROM TestIDs' 
     when ORDINAL_POSITION /3.0 = ORDINAL_POSITION / 3 
     then CHAR (13) + ' 
      FROM TestIDs
      UNION ALL 
     SELECT ' else ', ' end
     from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TestIDs'
     order by Ordinal_Posiiton
    --print @SQL
    execute(@SQL)
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Thursday, June 10, 2010 9:57 AM
    Friday, June 04, 2010 9:27 PM

All replies

  • Is there anything (prefix for example) in the data to distinguish an ID field from a Data field?

     

    Is there always the same number of Data fields for each ID field such that the ID fields are always every 3rd field?


    ----------------------------------------------------------- May we all make money in the sequel.
    Friday, June 04, 2010 8:55 PM
  • If the number of same columns is always known in advance (3 in this case) try this solution:

     

    create table TestIDS 
    (ID1 int, ID1Data1 int, ID1Data2 int, 
    ID2 int, ID2Data1 int, ID2Data2 int, 
    ID3 int, ID3Data1 int, ID3Data2 int,
    ID4 int, ID4Data1 int, ID4Data2 int)
    insert into TestIDS values (1,2,3,
    2,5,6,
    3,7,8,
    4,10,12)
    
    go
    
    declare @SQL nvarchar(max), @Total_Columns int
    select @Total_Columns = COUNT(1) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TestIDs'
     set @SQL = ' SELECT '
    select @SQL = @SQL + 
    
    Column_Name + 
     Case when Ordinal_Position = 1 then ' AS ID'
      when Ordinal_Position = 2 then ' AS Data1'
      when Ordinal_Position = 3 then ' AS Data2'
     else '' end +  
     Case when Ordinal_Position = @Total_Columns then '
      FROM TestIDs' 
     when ORDINAL_POSITION /3.0 = ORDINAL_POSITION / 3 
     then CHAR (13) + ' 
      FROM TestIDs
      UNION ALL 
     SELECT ' else ', ' end
     from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TestIDs'
     order by Ordinal_Posiiton
    --print @SQL
    execute(@SQL)
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Thursday, June 10, 2010 9:57 AM
    Friday, June 04, 2010 9:27 PM
  • Please, provide sample table with data and expected output
    Saturday, June 05, 2010 2:15 AM