none
Basic - select with fixed values - invert columns to rows RRS feed

  • Question

  • Very basic, but I just forgot :)

    select ('value1','value2','value3','value4','value5','value6','value7','value8','value9') results in 9 columns. How do I change the statement to get 1 column with 9 rows (each value in 1 row)?

     

    Thursday, December 16, 2010 5:36 PM

Answers

  •  

    select 'Value1' as Value
    
    UNION ALL
    
    select 'Value2' as Value
    
    UNION ALL
    
    select 'Value3' as Value
    

     


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

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Ai-hua Qiu Tuesday, December 21, 2010 5:11 AM
    • Marked as answer by Ai-hua Qiu Friday, December 24, 2010 8:49 AM
    Thursday, December 16, 2010 6:01 PM
    Moderator
  • In SQL Server 2008 T-SQL, you can do the following:

    SELECT * FROM (VALUES ('value1'),('value2'),('value3'),('value4'),('value5'),
                ('value6'),('value7'),('value8'),('value9')) A(SomeString)
    /*
    SomeString
    value1
    value2
    value3
    value4
    value5
    value6
    value7
    value8
    value9
    */
    

    Kalman Toth, Microsoft Community Contributor 2011; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    Wednesday, December 22, 2010 5:45 AM
    Moderator

All replies

  • Use "Unpivot " if SQL Version is 2005 and up.

    http://msdn.microsoft.com/en-us/library/ms177410(SQL.90).aspx

     

    Declare @myTable Table (ID TinyInt, Col1 SmallInt, Col2 SmallInt, Col3 SmallInt, Col4 SmallInt)
    
    Insert Into @myTable 
    Select 1, 10, 20, 30, 40 Union All
    Select 2, 50, 60, 70, 80
    
    Select 
    	ID, myValues, NewValue
    From 
    	(Select ID, Col1, Col2, Col3, Col4 From @myTable) main 
    	Unpivot 
    	(NewValue for myValues In (Col1, Col2, Col3, Col4)) As unpvt 
    Thursday, December 16, 2010 5:46 PM
  •  

    select 'Value1' as Value
    
    UNION ALL
    
    select 'Value2' as Value
    
    UNION ALL
    
    select 'Value3' as Value
    

     


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

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Ai-hua Qiu Tuesday, December 21, 2010 5:11 AM
    • Marked as answer by Ai-hua Qiu Friday, December 24, 2010 8:49 AM
    Thursday, December 16, 2010 6:01 PM
    Moderator
  • In SQL Server 2008 T-SQL, you can do the following:

    SELECT * FROM (VALUES ('value1'),('value2'),('value3'),('value4'),('value5'),
                ('value6'),('value7'),('value8'),('value9')) A(SomeString)
    /*
    SomeString
    value1
    value2
    value3
    value4
    value5
    value6
    value7
    value8
    value9
    */
    

    Kalman Toth, Microsoft Community Contributor 2011; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    Wednesday, December 22, 2010 5:45 AM
    Moderator