Need suggestions with this situation
-
I have a table with the following structure
Id , ProdId , ProdIdDetails
1 2 abc
2 2 def
3 3 xyz
4 2 ghi
5 4 pqr
6 5 lmn
7 5 opq
I want to display this data as follows where the id column is not required
ProdId, ProdIdDetails
2 abc,def,ghi
3 xyz
4 pqr
5 lmn,opq
In short I need to get the group by of my tables productId but the productDetails are to be displayed in a comma separated form.
Open to all suggestions!
-
Search this forum for "concatenating".
Choose one of hundreds suggestions.
_____________
Code for TallyGenerator -
Yeah, this really needs to end up in a sticky somewhere in the forum (maybe an FAQ?). If your search results aren't fruitful, this is the last post that I made on the subject, although I'm certain that dozens of people have posted similar methods both before and after me.
-
Thanks for the help,
I really wanted to have a re-discussion on this topic and find any factors that could optimize this situation.
And if we had something new in SQL - Server 2005 instore for this!
-
You might be able to use some form of recursive CTE in SQL Server 2005, but your request is in an SQL Server 2000 forum.
The UDF that David suggested is the classic method for solving this problem and will be just as fast as the CTE and, perhaps, a bit more flexible. You won't find any factors that could optimize this situation because it's a bad thing to do to a perfectly good database
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks you all for the input, I really appreciate it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply