Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    41

    Unanswered: unpivoting using T-sql

    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!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    Code:
    SELECT customer,product,qty
    FROM target
    UNPIVOT (qty FOR product IN (ChairQty,TableQty,FanQty,DesktopQty,PrinterQty,LaptopQty))p
    
    SELECT * FROM 
    (
    SELECT customer, 'Chair' AS Product ,ChairQty  AS qty FROM target
    UNION ALL
    SELECT customer, 'Table'  ,TableQty FROM target
    UNION ALL
    SELECT customer, 'Fan'  ,FanQty FROM target
    UNION ALL
    SELECT customer, 'Laptop'  ,LaptopQty FROM target
    UNION ALL
    SELECT customer, 'Desktop'  ,DesktopQty FROM target
    UNION ALL
    SELECT customer, 'Printer'  ,PrinterQty FROM target
    )s WHERE qty IS NOT NULL
    
    http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

  4. #4
    Join Date
    Nov 2007
    Posts
    41
    Thank You very much!

  5. #5
    Join Date
    Dec 2008
    Posts
    135
    Quote Originally Posted by frank.svs
    Thank You very much!
    Welcome

Posting Permissions

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