SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert column to row


Convert column to row

Author
Message
lyndon 19575
lyndon 19575
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23615 Visits: 9730
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
lyndon 19575
lyndon 19575
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
Thanks a lot GSquared, that worked.

I will keep scripts in mind in future.

Regards,

Lyndon
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15001 Visits: 14396
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search