T-SQL - how to swap multiple rows to one column

I have a resultset structure like this

code    Value
1       A0001
1       A0002
2       B0001
2       B0002
2       B0003
3       C0001
4       D0001
4       D0002
..       ...

and I want to get to this:

ID     Response
1       A0001, A0002
2       B0001, B0002, B0003
3       C0001
4       D0001, D0002
...       ...

using 开发者_运维技巧SQL. Is there a way to do this using the T-SQL? (and PL/SQL)

thanks very much!

---

thanks t-clausen.dk

It work. But I want to use a one query( may be with sub-query). how can i do it?

---

For T-SQL I would use something like this:

;with sample_data AS
(
SELECT 1 as code,'A0001' as [value]
UNION ALL SELECT 1,'A0002'
UNION ALL SELECT 2,'B0001'
UNION ALL SELECT 2,'B0002'
UNION ALL SELECT 2,'B0003'
UNION ALL SELECT 3,'C0001'
UNION ALL SELECT 4,'D0001'
UNION ALL SELECT 4,'D0002'
)
SELECT
code
,STUFF((
    SELECT
      ', ' + sd2.[value]
    FROM sample_data sd2
    WHERE sd1.code = sd2.code
    FOR XML PATH('')
    ), 1, 1, ''
  )
FROM sample_data sd1
GROUP BY sd1.code

AS for the PL/SQL no idea I'm afraid!

---

This was written for mssql and changed to what i hope is oracle syntax. Please let me know if it works.

declare @t table(code int, value varchar(20))
insert @t values(1,'A0001') 
insert @t values(1,'A0002') 
insert @t values(2,'B0001')
insert @t values(2,'B0002') 
insert @t values(2,'B0003') 
insert @t values(3,'C0001') 
insert @t values(4,'D0001') 
insert @t values(4,'D0002') 

;with a as
(select code, 
row_number() over (partition by code order by value) rna, 
row_number() over (partition by code order by value desc) rnd, 
value 
from @t
), b as
(
select code, rnd, value, rna, cast(value as varchar(2000)) outvalue from a where rna = 1
union all
select a.code, a.rnd, a.value, a.rna, cast(b.outvalue || ',' || a.value as varchar(2000))
from a 
join b on a.rna - 1 = b.rna and a.code = b.code
)
select code, outvalue from b
where rnd = 1
order by 1
--option (maxrecursion 0) -- not sure this is pl/sql

---

For PL/SQL

SELECT DISTINCT CODE as ID, LISTAGG(VALUE, ',')  
       WITHIN GROUP(ORDER BY code) OVER(PARTITION BY CODE) AS Response
       FROM TableName ORDER BY CODE;