locked
PIVOT with no column names? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    How do I take this statement:

    SELECT 'a', 'b', 'c'

    ...and pivot the results so 3 rows, not 3 columns.  PIVOT is how I would do a table, but this has no column names.  Putting AS after each value to give each column a name may be possible, but impractical for my current task.

    Just curious if it's possible to pivot the above statement alone.

    Tuesday, December 18, 2012 1:57 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    If you need to convert columns to rows, it's called UNPIVOT.

    Try

    select * from (values ('a'),('b'),('c')) F(Col)

    SQL Server 2008 and up solution only.

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


    My blog

    • Proposed as answer by Chintak Chhapia Tuesday, December 18, 2012 2:12 PM
    • Marked as answer by Mini Button Tuesday, December 18, 2012 2:15 PM
    Tuesday, December 18, 2012 2:03 PM

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote

    If you need to convert columns to rows, it's called UNPIVOT.

    Try

    select * from (values ('a'),('b'),('c')) F(Col)

    SQL Server 2008 and up solution only.

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


    My blog

    • Proposed as answer by Chintak Chhapia Tuesday, December 18, 2012 2:12 PM
    • Marked as answer by Mini Button Tuesday, December 18, 2012 2:15 PM
    Tuesday, December 18, 2012 2:03 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    You need to unpivot. See if this helps

    select c from
(
SELECT 'a' ca, 'b' cb, 'c' cc
) as p
unpivot
( c for e in (ca,cb,cc )
) as unpvt

    - Chintak (My Blog)

    Tuesday, December 18, 2012 2:11 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Of course I ended up making an simple yet overly complicated XML-based solution in the mean time... then comes Naomi with her experience to show me the best way!  Thanks again, Naomi :)  I had no idea you could "select from values" for some derived table.
    Tuesday, December 18, 2012 2:15 PM
  • Question
    Sign in to vote
    1
    Sign in to vote
     I learned this trick first from Jacob Sebastian site

    http://beyondrelational.com/modules/2/blogs/70/posts/10905/interesting-enhancements-to-the-values-clause-in-sql-server-2008.aspx

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


    My blog


    • Edited by Naomi N Tuesday, December 18, 2012 4:02 PM
    Tuesday, December 18, 2012 2:16 PM