Link to home
Start Free TrialLog in
Avatar of KoolAsIce
KoolAsIceFlag for Afghanistan

asked on

Concatenate ProductName and SubProductName separated by comma

Hello Experts,

I am not completely new to sql but not familiar with everything as well. I am stumbled with one problem which I thought was easy :)

Anyways, here is the senario:
I have two tables named Products and SubProducts. Each product is related to some SubProducts. What I am trying to accomplish is return ProductName and SubProductName in following manner.

ProductNameOne|SubProductNameOne|SubProductNameTwo|SubProductNameThree

ProductNameTwo|SubProductNameOne|SubProductNameTwo|SubProductNameThree

ProductNameThree|SubProductNameOne|SubProductNameTwo|SubProductNameThree  

.......hope you get the idea. so concatenate ProductName and SubProductNames that are related to that ProductName.

So row one would contain concatenation of first ProductName and it's related SubProductNames
Rows two would contain concatenation of second ProductName and it's related SubProductNames....

Any ideas/suggestions are highly welcome.  

Thank you in advance for all you guys help :)
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image
This will do it for you:
Replace anything in the <>

SELECT cast(p.<PARENTCOL>as varchar(50)) + ',' + el.values
FROM <PARENT TABLE> p
CROSS APPLY (SELECT cast(<CHILDCOL> as varchar(20)) + ',' AS [text()]
             FROM <CHILDTABLE>
             WHERE <PARENTCOL_ID = ChildTable_ParentCOL ID (FK>
             FOR XML PATH(''))el(values)


ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of KoolAsIce
KoolAsIce
Flag of Afghanistan image

ASKER

You are genius! I was trying to do same thing using three temporary tables and almost 100 lines of code!

Thank you so much for your help!
Avatar of KoolAsIce
KoolAsIce
Flag of Afghanistan image

ASKER

Can someone tell me how to avoid '&' displaying as &amp;

In the attached code, the column SubCategoryName has a name containing '&' and result is displayed as '&amp;'

I assume it's because of the use of FOR XML PATH(' ').

All the help would be greatly appreciated on how to avoid that.