Results 1 to 14 of 14

Thread: Pivot Data

  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Pivot Data

    I have data in this format
    Code:
    Product    qty
    ABC          4
    DEF          5
    :
    :
    XYZ         4
    I'll never have more that 10 products at a time.
    I need to deliver the data in a format like this:
    Code:
    prod_1  qty1  prod_2  qty2 ... prod_10  qty10
    ABC      4      DEF      5        XYZ      4
    If there are less than 10 products, the last columns will contain NULL.
    What I'm doing is delivering a recordset to a report.
    The report is designed to work with a single record, not multiple lines.
    Of course, there's more data than this, this is just where I'm stuck.

    Is there a way to pivot this data? I'm using SQL2000

    Here's a ddl of what I've been trying to work with,
    but all attempts at pivoting have been = FAIL
    Code:
    CREATE TABLE #TMP (
    	PKG_CODE    VARCHAR(20),
    	QTY_ACT  INTEGER)
    
    INSERT INTO #TMP
    SELECT 'PAD', 15
    UNION ALL
    SELECT 'PAD4436', 15
    UNION ALL
    SELECT 'SW', 15
    UNION ALL
    SELECT 'TS', 16
    
    SELECT * FROM #TMP
    
    DROP TABLE #TMP
    Inspiration Through Fermentation

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How do you decide what product goes into which column?
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Hi george,

    Ordering doesn't matter, the first piece of data goes in the first column.

    I'm thinking I'll have to use a cursor here. It doesn't matter... there won't be any speed issues, I'm just wondering if it's possible with SQL.

    Thanks
    Mark
    Inspiration Through Fermentation

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Nah, I'm pretty sure that it can be done.
    Code:
    CREATE TABLE #foo (
       product		VARCHAR(10)
       PRIMARY KEY (product)
    ,  qty			INT
       )
    
    DECLARE @j0		VARCHAR(10)
    ,  @j1			VARCHAR(10)
    
    INSERT INTO #foo (
       product, qty
       ) SELECT         'A01', 11
       UNION ALL SELECT 'B02', 22
       UNION ALL SELECT 'C03', 33
       UNION ALL SELECT 'D04', 44
       UNION ALL SELECT 'E05', 55
       UNION ALL SELECT 'F06', 66
       UNION ALL SELECT 'G07', 77
       UNION ALL SELECT 'H08', 88
       UNION ALL SELECT 'I09', 99
       UNION ALL SELECT 'J10', 10
    
    SELECT
       z00.product, z00.qty,  z01.product, z01.qty
    ,  z02.product, z02.qty,  z03.product, z03.qty
    ,  z04.product, z04.qty,  z05.product, z05.qty
    ,  z06.product, z06.qty,  z07.product, z07.qty
    ,  z08.product, z08.qty,  z09.product, z09.qty
       FROM #foo AS z00, #foo AS z01, #foo AS z02, #foo AS z03, #foo AS z04
    ,       #foo AS z05, #foo AS z06, #foo AS z07, #foo AS z08, #foo AS z09
       WHERE  z00.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 0)
          AND z01.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 1)
          AND z02.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 2)
          AND z03.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 3)
          AND z04.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 4)
          AND z05.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 5)
          AND z06.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 6)
          AND z07.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 7)
          AND z08.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 8)
          AND z09.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 9)
    
    DROP TABLE #foo
    -PatP

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    sooo close...

    That works great, except when I have less than 10 records - which I normally will. I get nothing if I comment out the last UNION statement.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i expect if you used left outer joins instead of (implied) inner, it might work

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Works perfectly
    Code:
    INSERT INTO #foo (
       product, qty
       ) SELECT         'A01', 11
       UNION ALL SELECT 'B02', 22
    --   UNION ALL SELECT 'C03', 33
    --   UNION ALL SELECT 'D04', 44
    --   UNION ALL SELECT 'E05', 55
    --   UNION ALL SELECT 'F06', 66
    --   UNION ALL SELECT 'G07', 77
    --   UNION ALL SELECT 'H08', 88
    --   UNION ALL SELECT 'I09', 99
    --   UNION ALL SELECT 'J10', 10
    select z00.product, z00.qty, 
    z01.product, z01.qty, 
    z02.product, z02.qty, 
    z03.product, z03.qty
    from #foo as z00 left join #foo as z01 on z00.product<z01.product 
    left join #foo as z02 on z01.product<z02.product 
    left join #foo as z03 on z02.product<z03.product
    WHERE  z00.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 0)
          AND z01.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 1)
          AND (z02.product is null or z02.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 2))
         AND (z03.product is null or z03.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 3))
    Thanks to both of you!
    Inspiration Through Fermentation

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, what i had in mind was this --
    Code:
      FROM #foo AS z00
    left outer          
      join #foo AS z01  
        on z01.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 1)         
    left outer                                                                                                                                                       
      join #foo AS z02                                                                                                                                               
        on z02.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 2)         
    left outer                                                                                                                                                       
      join #foo AS z03                                                                                                                                               
        on z03.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 3)         
    left outer                                                                                                                                                       
      join #foo AS z04                                                                                                                                             
        on z04.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 4)         
    left outer                                                                                                                                                       
      join #foo AS z05                                                                                                                                         
        on z05.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 5)         
    left outer                                                                                                                                                       
      join #foo AS z06                                                                                                                                               
        on z06.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 6)         
    left outer                                                                                                                                                       
      join #foo AS z07                                                                                                                                               
        on z07.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 7)         
    left outer                                                                                                                                                       
      join #foo AS z08                                                                                                                                               
        on z08.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 8)         
    left outer                                                                                                                                                       
      join #foo AS z09                                                                                                                                 
        on z09.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 9)
     WHERE z00.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 0)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My bad! I don't often need to "think Oracle" anymore, but this is a case where SQL-89 is an elegant solution (if you do it right). A solution that would better match my original intent would be:
    Code:
    CREATE TABLE #foo (
       product		VARCHAR(10)
       PRIMARY KEY (product)
    ,  qty			INT
       )
    
    DECLARE @j0		VARCHAR(10)
    ,  @j1			VARCHAR(10)
    
    INSERT INTO #foo (
       product, qty
       ) SELECT         'A01', 11
       UNION ALL SELECT 'B02', 22
       UNION ALL SELECT 'C03', 33
       UNION ALL SELECT 'D04', 44
       UNION ALL SELECT 'E05', 55
       UNION ALL SELECT 'F06', 66
       UNION ALL SELECT 'G07', 77
       UNION ALL SELECT 'H08', 88
       UNION ALL SELECT 'I09', 99
    --   UNION ALL SELECT 'J10', 10
    
    SELECT
       z00.product, z00.qty,  z01.product, z01.qty
    ,  z02.product, z02.qty,  z03.product, z03.qty
    ,  z04.product, z04.qty,  z05.product, z05.qty
    ,  z06.product, z06.qty,  z07.product, z07.qty
    ,  z08.product, z08.qty,  z09.product, z09.qty
       FROM #foo AS z00, #foo AS z01, #foo AS z02, #foo AS z03, #foo AS z04
    ,       #foo AS z05, #foo AS z06, #foo AS z07, #foo AS z08, #foo AS z09
       WHERE  z00.product = (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 0)
          AND z01.product =* (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 1)
          AND z02.product =* (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 2)
          AND z03.product =* (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 3)
          AND z04.product =* (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 4)
          AND z05.product =* (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 5)
          AND z06.product =* (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 6)
          AND z07.product =* (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 7)
          AND z08.product =* (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 8)
          AND z09.product =* (SELECT product FROM #foo AS a WHERE (SELECT Count(*) FROM #foo AS b WHERE b.product < a.product) = 9)
    
    DROP TABLE #foo
    -PatP

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "better match my original intent?"

    isn't mine the same as your latest, only you're not using sql-89 and i am?

    and what does oracle have to do with any of this?

    forgive me for asking, but i guess i didn't understand your remarks at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm 99 5/8 percent sure that my example was using SQL-89 (doing pseudo-joins within the WHERE clause) and that yours was using SQL-92 (doing explicit joins within the FROM clause). The SQL-92 approach is usually more comfortable for me, but in this case it seems more verbose and the older syntax seems clearer to me.

    Oracle uses a very row-oriented approach to data. This is why it depends on cursors for so much, and why its query optimizer works better for pseudo-joins expressed as criteria in the WHERE clause instead of set oriented joins expressed within the FROM clause. This is why Oracle is sometimes easier to code for instances like this because the criteria don't get applied until after the candidate result set has materialized, so you can use any valid SQL expression to specify what rows you want returned instead of the more efficient but more limited choices available before the candidate set is materialized.

    The purpose behind pusing the join criteria out of the WHERE clause into the FROM clause was to make it possible for more advanced optimizers to process the join operations and specifications in whatever order they chose. This was to allow more advanced ways of processing JOIN operations that could drastically improve the efficency of the operation (and thereby improve the efficiency of the databse engine).

    -PatP

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i might have missed something somewhere along the way, i could be wrong, i don't want to say you're mistaken, but that reprehensible "equals asterisk" syntax was never part of any sql standard

    which is why your earlier statement confused me as to whose query you were talking about

    "equals asterisk" is, i believe, a sybase/microsoft implementation

    which, admittedly, is fair game for this particular forum

    but it isn't sql-89
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't have a copy of the ANSI SQL-89 standard handy, but I don't think that it had a specification for outer joins at all. Only the vendor specific extensions allowed outer joins at that time.

    Oracle had its =(+) syntax, DB2 had its =| syntax, and all of the other SQL vendors (Sybase, Gupta, Borland, etc) that I knew of supported the =* syntax. None of them that I knew of supported JOIN operations in the FROM clause, all of them either completely or partially materialized the candidate result set, then started to apply the WHERE clause against that result set.

    -PatP

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, as you yourself so eloquently said, "this is a case where SQL-89 is an elegant solution (if you do it right)"

    the trick, of course, would be doing it right

    which in my opinion would be to use sql-92 syntax

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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