SQL query question - pivot or Cross TAB
table:
product_code varchar(12)
product_notes varchar(50)
table has many entries for same product code with many notes.
query needs to display in one row:
product_code and all the Product_notes separated by a comma or in different fields,
Max number of notes would be 20.
e.g.
data in table:
product_code product_notes
a1234 ordered 25000 at special price
a1234 returned 200 ft bad stock
a1234 got rebate of $275
b3444 new price 44.45 a box after 4/22011
query result:
a1234 ordered 25000 at special price,returned 200 ft bad stock,got rebate of $275
b3444 new price 44.45 a box after 4/22011
thanks
product_code varchar(12)
product_notes varchar(50)
table has many entries for same product code with many notes.
query needs to display in one row:
product_code and all the Product_notes separated by a comma or in different fields,
Max number of notes would be 20.
e.g.
data in table:
product_code product_notes
a1234 ordered 25000 at special price
a1234 returned 200 ft bad stock
a1234 got rebate of $275
b3444 new price 44.45 a box after 4/22011
query result:
a1234 ordered 25000 at special price,returned 200 ft bad stock,got rebate of $275
b3444 new price 44.45 a box after 4/22011
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect. Great solution. thanks
(SELECT ' ' + s.product_notes
FROM products s
where s.product_code = p.product_code
ORDER BY s.product_notes
FOR XML PATH('')),2,200000) AS CSV
from products p