?
Solved

SQL query question  - pivot or Cross TAB

Posted on 2012-08-21
3
Medium Priority
?
561 Views
Last Modified: 2012-08-21
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
0
Comment
Question by:john
  • 2
3 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38318181
SELECT distinct product_code, SUBSTRING(
(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
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 38318186
SELECT distinct product_code, SUBSTRING(
(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
0
 

Author Closing Comment

by:john
ID: 38318848
Perfect. Great solution. thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question