May 22, 2008 at 2:14 pm
Current ResultSet
------------------
ColA ColB
TEST1 111
TEST1 222
TEST2 333
TEST3 444
TEST3 555
I am currently outer-joiing two tables to retrive some data in the above format. My intent is to modify the query so I can retrive the data in below fashion.
Intended ResultSet
------------------
ColA ColB
TEST1 111,222
TEST2 333
TEST3 444,555
Can someone please assist with this?
May 22, 2008 at 2:26 pm
I'm not trying to be difficult here, but what are you planning on doing with it once you have it in that format? Most people are struggling to get their data out of that format because it is less usable. I'm just curious as to what having ColB in a comma delimited list is going to do for you?
May 22, 2008 at 3:08 pm
something like this will do it:
select ColA,
stuff( (select ','+B.ColB
from Data B
where B.ColA = A.ColA
order by B.ColB
for xml path('')),
1,1,'')
from
(select distinct ColA from Data) as A
May 22, 2008 at 5:53 pm
Hi, Thanks for the reply. I need something in line with your reply. But I still could not achieve it with your tips. I need further help if you would not mind looking into details of the actual table and guide me in the right way. Pls excuse me for not providing enough details on the table structures to start with....
Table1
------
ColA ColB ColC
seq1 A1 V1
seq2 A2 V2
seq3 A3 V3
Table2
-------
ColA ColB
seq1 B1
seq1 B2
seq2 B3
seq3 B4
seq3 B5
I need the query return to be in the format below....
ResultSet
---------
Col1 Col2
V1 B1,B2
V2 B3
V3 B4, B5
I would appreciate your help with this. Thank you in advance.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply