October 8, 2013 at 11:35 am
Hi,
Is it possible to achieve result that illustrated below in nice single query? I started doing pivot, but still missing this concatenation, other whay go with XML Path??
bit lost here.
THanks all to all
;with t as (select * from (
select 21 id1, 200 id2, 'prod1' prodID union
select 21 id1, 200 id2, 'prod22' prodID union
select 21 id1, 200 id2, 'prod333' prodID union
select 11 id1, 400 id2, 'prod11' prodID union
select 11 id1, 400 id2, 'prod22' prodID union
select 11 id1, 900 id2, 'prod01' prodID union
select 33 id1, 300 id2, 'prod3' prodID ) a )
-- select * From t
id1 | id2 | ProdIDs |
21 | 200 | prod1, prod22, prod33 |
11 | 400 | prod11, prod22 |
11 | 900 | prod01 |
33 | 300 | prod3 |
-- group by id1, id2
October 8, 2013 at 11:52 am
SELECT
id1,
id2,
STUFF(
(
SELECT ',' + prodID
FROM t p2
WHERE p1.id1 = p2.id1
AND p1.id2 = p2.id2
ORDER BY p2.prodID
FOR XML PATH('')),1,1,' ') ProdIds
FROM t p1
GROUP BY id1,
id2
ORDER BY id2
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 8, 2013 at 11:54 am
Hi,all
I think I have success story, figured it out
THanks Livingston for your post, just saw yours too, thanks all support
select id1, id2,
( SELECT --id1, id2,
SUBSTRING(
(SELECT ',' + t.prodID
FROM t
WHERE t.id1 = t2.id1 and t.id2 = t2.id2
FOR XML PATH('')),
2,
200000) AS CSV )
from t t2
-- where WHERE t.id1 = 21
group by id1, id2
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply