Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    1

    Unanswered: how do you separate records from single column into several columns?

    hi, I would like to take records from a single column, say SALES, and separate them into several columns depending on the product type.

    How would I go about doing this? Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I think you mean to pivot a recordset. This can be done easily in Excel, I think, but it is difficult in SQL Server. At least in any robust way,. SQL 2005 has a PIVOT function, but requires hard-coding the column names, if I recall.

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    If you have a fairly small list of product types, that rarely changes, you can hard-code a series of CASE statements.

    A more proper methodology would be to use the PIVOT syntax, as MCrowely has suggested.

    The PIVOT syntax is VERY difficult to deal-with-I'm not sure that I have yet fully figured it out, but it is pretty powerful.


    BTW, MCrowley, I have written stored procedures with the PIVOT syntax which creates the column names dynamically so the result set can have a differing number of columns every time you run the report.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by PracticalProgram View Post
    If you have a fairly small list of product types, that rarely changes, you can hard-code a series of CASE statements.

    A more proper methodology would be to use the PIVOT syntax, as MCrowely has suggested.

    The PIVOT syntax is VERY difficult to deal-with-I'm not sure that I have yet fully figured it out, but it is pretty powerful.


    BTW, MCrowley, I have written stored procedures with the PIVOT syntax which creates the column names dynamically so the result set can have a differing number of columns every time you run the report.
    I've done the same with Pivot and using dynamic sql, extremely fast.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Pivoting is really a matter of data presentation, and as such should not even be performed by the database. Leave it to your application or reporting software to do this.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •