如下表：
styleid colorid sizeid
AA125094 82 L
AA125094 82 M
AA125094 82 S
AA125094 82 XL
AA125094 E5 L
AA125094 E5 M
AA125094 E5 S
AA125094 E5 XL
AA125601 71 L
AA125601 71 M
AA125601 71 S
AA125601 77 L
AA125601 77 M
AA125601 77 S
查询后要获得如下结果：
styleid colorid sizeid
AA125094 82，E5 L,M,S,XL
AA125601 71,77 L,M,S
不知道能实现否？
------解决方案--------------------
select distinct,
styleid,
stuff((select ','+CAST(colorid as varchar)
from tb t2
where t1.styleid = t2.styleid
group by colorid
For xml path('')
),1,1,'') as colorid,
stuff((select ','+sizeid
from tb t2
where t1.styleid = t2.styleid
group by sizeid
For xml path('')
),1,1,'') as sizeid
from tb t1
------解决方案--------------------
--drop table tb
--go
create table tb(styleid varchar(20),colorid varchar(10),sizeid varchar(10))
insert into tb
select 'AA125094', '82', 'L'
union all select 'AA125094', '82' ,'M'
union all select 'AA125094', '82' ,'S'
union all select 'AA125094', '82' ,'XL'
union all select 'AA125094', 'E5' ,'L'
union all select 'AA125094', 'E5' ,'M'
union all select 'AA125094', 'E5' ,'S'
union all select 'AA125094', 'E5' ,'XL'
union all select 'AA125601', '71' ,'L'
union all select 'AA125601', '71' ,'M'
union all select 'AA125601', '71' ,'S'
union all select 'AA125601', '77' ,'L'
union all select 'AA125601', '77' ,'M'
union all select 'AA125601', '77' ,'S'
select distinct
styleid,
stuff((select ','+colorid
from tb t2
where t1.styleid = t2.styleid
group by colorid
For xml path('')
),1,1,'') as colorid,
stuff((select ','+sizeid
from tb t2
where t1.styleid = t2.styleid
group by sizeid
For xml path('')
),1,1,'') as sizeid
from tb t1
/*
styleid colorid sizeid
AA125094 82,E5 L,M,S,XL
AA125601 71,77 L,M,S
*/
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-25 13:02:14
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)