September 10, 2010 at 2:05 pm
Hi !!
I have 2 tables
Table 1
ID Description
----------------
1 'DATO UNO'
2 'DATO DOS'
Table 2
ID_table1 Service
-----------------
1 2214
1 2222
2 2102
2 2103
What I need is:
COLUM1 COLUM2 COLUM2
--------------------------------
1 'DATO UNO' '2214,2222'
2 'DATO DOS' '2103,2103'
Exists a special select statement to do that?
September 10, 2010 at 3:06 pm
Here's a solution that I think does what you're looking for.
For an explanation of what's going on here, I recommend reading through this thread (especially GSquared and RBarryYoung's explanations) since I can't really explain it well myself: http://www.sqlservercentral.com/Forums/Topic679572-338-1.aspx#bm679589
create table #table1 (ID int, [Description] char(8))
create table #table2 (ID_table1 int, [Service] char(4))
insert into #table1
select 1, 'DATO UNO' union all
select 2, 'DATO DOS'
insert into #table2
select 1, '2214' union all
select 1, '2222' union all
select 2, '2102' union all
select 2, '2103'
;with cte as
(
select
ID_table1,
[Service] = stuff((select ', ' + [Service] from #table2 t2 where t2.ID_table1 = t1.ID_table1 for xml path('')),1,2,'')
from #table2 t1
group by ID_table1
)
select
COLUM1 = t1.ID,
COLUM2 = t1.[Description],
COLUM3 = t2.[Service]
from #table1 t1
join cte t2
on t1.ID = t2.ID_table1
drop table #table1
drop table #table2
- Jeff
September 10, 2010 at 4:07 pm
A variation using CROSS APPLY
SELECT #table1.ID, #table1.Description, Z.Services
FROM #table1
CROSS APPLY
(
SELECT
STUFF((SELECT ',' + Service
FROM #table2
WHERE #table2.ID_table1 = #table1.ID
FOR XML PATH(''), TYPE).value('.[1]', 'varchar(MAX)'), 1, 1, '')
) AS Z (Services)
September 13, 2010 at 9:45 am
Thanks a lot!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply