Home Forums SQL Server 2008 T-SQL (SS2K8) Converting particular set of Rows as Columns RE: Converting particular set of Rows as Columns

  • May 1, 2013 at 10:19 pm

    #1611776

    gadde (5/1/2013)

    Converting one row as one Column is possible using Pivot. But is there any way to convert particular set of rows to one column?

    For example, below is the data table

    Customer---------------Product-------------Qty

    Jain --------------- Mango -------------10

    Charlie --------------- Orange--------------5

    Rocky --------------- Mango---------------6

    Rocky ---------------- Orange---------------3

    Expected Result set

    Customer---------------Mango---------------Orange---------------Mango and Orange

    Jain---------------------10-------------------NULL ---------------------NULL

    Charlie------------------NULL-------------------5-----------------------NULL

    Rocky-------------------NULL-----------------NULL---------------------- 9

    Appreciate any help

    * ---- are used for identation purpose only

    Thank You 🙂

    What you have there is actually a Pivot or, in old terms, a Cross Tab. Here's a couple of articles that explain (step by step in simple terms) how to hardcode one and how to do it dynamically on the other.

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)