Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert column to row Expand / Collapse
Author
Message
Posted Tuesday, August 21, 2012 9:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 3:10 AM
Points: 2, Visits: 1
Hello everyone,

I am a newbie to sql and I am faced with the problem below.

I have 3 tables

Categories
Cat_ID | Cat_Name
1 | Cosmetics
2 | Hair
3 | Furniture
4 | Office

Products
Prod_ID | Prod_name
1 | Chair
2 | Gel


PRoduct_categories
Prod_ID | Cat_id
1 | 3
1 | 4
2 | 1
2 | 2

I need a query that will give me the following
Product | Categories
Chair | Furniture , Office
Gel | Cosmetics, Hair

Any help would be greatly appreciated

Regards,

Lyndon
Post #1347864
Posted Tuesday, August 21, 2012 9:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 5, 2014 10:31 AM
Points: 13,872, Visits: 9,599
What you want for that is something like this:

CREATE TABLE #Categories
(Cat_ID INT,
Cat_Name VARCHAR(25));

INSERT INTO #Categories
(Cat_ID, Cat_Name)
VALUES (1, 'Cosmetics'),
(2, 'Hair'),
(3, 'Furniture'),
(4, 'Office');

CREATE TABLE #Products
(Prod_ID INT,
Prod_Name VARCHAR(25));

INSERT INTO #Products
(Prod_ID, Prod_Name)
VALUES (1, 'Chair'),
(2, 'Gel');

CREATE TABLE #Product_Categories (Prod_ID INT, Cat_ID INT);

INSERT INTO #Product_Categories
(Prod_ID, Cat_ID)
VALUES (1, 3),
(1, 4),
(2, 1),
(2, 2);

SELECT Prod_Name,
STUFF((SELECT ',' + Cat_Name
FROM #Categories AS C
INNER JOIN #Product_Categories AS PC
ON C.Cat_ID = PC.Cat_ID
WHERE PC.Prod_ID = #Products.Prod_ID
FOR XML PATH(''),
TYPE).value('.[1]', 'varchar(1000)'), 1, 1, '') AS Categories
FROM #Products;

For future reference, it's very helpful on your part if you were to provide table scripts and data like I did. Makes it much easier for us to help you. No big deal in a simple case like this, but it does make it easier.

The way my final query works is:

The outer query just pulls the Prod_Name and Prod_ID from the #Products table. That's simple.

The inline sub-query uses the Prod_ID in the outer query to pull the Cat_Name values from the other two tables (that's the Where clause), then uses a trick with For XML Path to turn the data into a list with commas. If you look up "t-sql for xml path" online, you'll find articles on how to do that and how it works. Then, it finally runs a Stuff() function to get rid of the comma at the beginning of the list.

Sub-queries like this, that use a value from the outer query, are called "correlated sub-queries". You can also search online for that, and you'll find more information about how they work, what they're for, and what rules apply to them.

Does that help?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1347874
Posted Wednesday, August 22, 2012 3:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 3:10 AM
Points: 2, Visits: 1
Thanks a lot GSquared, that worked.

I will keep scripts in mind in future.

Regards,

Lyndon
Post #1348274
Posted Monday, August 27, 2012 1:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
If you can use the SQLCLR here is an option that is a bit more expressive in terms of the syntax:

SELECT  p.Prod_Name,
dbo.GROUP_CONCAT(c.Cat_Name)
FROM #Products p
JOIN #Product_Categories pc ON p.Prod_ID = pc.Prod_ID
JOIN #Categories c ON pc.Cat_ID = c.Cat_ID
GROUP BY Prod_Name

You can download the dbo.GROUP_CONCAT function here: http://groupconcat.codeplex.com


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1350562
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse