I have data in this format
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:
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
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 #TMPHow do you decide what product goes into which column?|||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|||Nah, I'm pretty sure that it can be done.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|||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.|||i expect if you used left outer joins instead of (implied) inner, it might work
:)|||Works perfectly:)
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!|||actually, what i had in mind was this -- 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)|||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: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|||"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|||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|||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|||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|||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
:)
