Post reply

UNPIVOT using T-SQL

  • July 16, 2009 at 10:50 pm

    #136186

    Hi Frns,

    Need small help.

    Using source data i able to do PIVOTing. In the same way i need a query without using PIVOT keywords (i.e i need it using pure T-sql).

    Pl help me out!

    CREATE TABLE [dbo].[Customer](

    [customerName] [varchar](50) NULL,

    [product] [varchar](50) NULL,

    [qty] [smallint] NULL

    )

    INSERT INTO [Customer]

    SELECT 'Tom','Table',3

    UNION ALL

    SELECT 'Tom','Chair',4

    UNION ALL

    SELECT 'Tom','Fan',5

    UNION ALL

    SELECT 'Ryan','Printer',5

    UNION ALL

    SELECT 'John','Desktop',9

    CREATE TABLE [dbo].[Target](

    [customer] [varchar](50) NULL,

    [ChairQty] [smallint] NULL,

    [TableQty] [smallint] NULL,

    [FanQty] [smallint] NULL,

    [LaptopQty] [smallint] NULL,

    [DesktopQty] [smallint] NULL,

    [PrinterQty] [smallint] NULL

    )

    insert into target

    SELECT

    customerName as customer,

    SUM(CASE when product = 'Chair' then qty else NULL end) as ChairQty,

    SUM(CASE when product = 'Table' then qty else NULL end) as TableQty,

    SUM(CASE when product = 'Fan' then qty else NULL end) as FanQty,

    SUM(CASE when product = 'Laptop' then qty else NULL end) as LaptopQty,

    SUM(CASE when product = 'Desktop' then qty else NULL end) as DesktopQty,

    SUM(CASE when product = 'Printer' then qty else NULL end) as PrinterQty

    FROM Customer

    GROUP BY

    customerName

    select * from customer

    select * from target

    Using target i need to get back the Customer Data?

    Thanks in Advance!

  • July 16, 2009 at 10:56 pm

    #1026006

    mahesh.vsp (7/16/2009)

    Using target i need to get back the Customer Data?

    quote]

    Hi,

    You mean reversing of the pivot?

  • July 16, 2009 at 10:58 pm

    #1026007

    Since you have done the pivot the hard way, here's the equivalent to unpivot...

    insert into Customer (customerName, product, qty)

    select customer, 'Chair', ChairQty from Target

    union select customer, 'Table', TableQty from Target

    ...{union the other selects...}...

    union select customer, 'Printer', PrinterQty from Target

  • July 16, 2009 at 11:10 pm

    #1026009

    Mansfield (7/16/2009)

    Since you have done the pivot the hard way, here's the equivalent to unpivot...

    insert into Customer (customerName, product, qty)

    select customer, 'Chair', ChairQty from Target

    union select customer, 'Table', TableQty from Target

    ...{union the other selects...}...

    union select customer, 'Printer', PrinterQty from Target

    Hi,

    Nice one, but you add the where condition

    Like

    select customer, 'Chair', ChairQty from target where ChairQty >0

    union

    select customer, 'Table', TableQty from target where TableQty >0

    union

    select customer, 'Fan', FanQty from target where FanQty >0

    union

    select customer, 'Desktop', DesktopQty from target where DesktopQty >0

    union

    select customer, 'Printer', PrinterQty from target where PrinterQty >0

  • July 17, 2009 at 10:31 am

    #1026311

    Thank You all!

  • July 17, 2009 at 10:50 am

    #1026322

    Mansfield (7/16/2009)

    Since you have done the pivot the hard way, here's the equivalent to unpivot...

    Before you go calling it the "hard way", you might want to read up on it especially where performance comes into play. 😉

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

    --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)

  • July 18, 2009 at 9:36 pm

    #1026619

    Thanks Jeff.

  • July 19, 2009 at 8:42 am

    #1026632

    You bet, Mahesh. Thank you for the feedback.

    --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)

  • July 19, 2009 at 2:38 pm

    #1026659

    Jeff, your article pretty much states why I consider pivoting at the SQL level the "hard" way - in the sense that this should be ideally be done in the presentation layer. Ideals being what they are, however... 🙂

  • July 19, 2009 at 3:50 pm

    #1026661

    Mansfield (7/19/2009)

    Jeff, your article pretty much states why I consider pivoting at the SQL level the "hard" way - in the sense that this should be ideally be done in the presentation layer. Ideals being what they are, however... 🙂

    Roger THAT! I thought you were talking about PIVOT being easier than a Cross-Tab.

    --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)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply