一个查询汇总的问题
如下表：
styleid colorid sizeid
AA12509482L
AA12509482M
AA12509482S
AA12509482XL
AA125094E5L
AA125094E5M
AA125094E5S
AA125094E5XL
AA12560171L
AA12560171M
AA12560171S
AA12560177L
AA12560177M
AA12560177S
查询后要获得如下结果：
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 coloridsizeid
AA12509482,E5L,M,S,XL
AA12560171,77L,M,S
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-25 13:02:14
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([styleid] varchar(8),[colorid] varchar(2),[sizeid] varchar(2))
insert [huang]
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 a.[styleid],
stuff((select DISTINCT ','+[colorid] from [huang] b
where b.[styleid]=a.[styleid]
for xml path('')),1,1,'') 'colorid'
,
stuff((select DISTINCT ','+[sizeid] from [huang] b
where b.[styleid]=a.[styleid]
for xml path('')),1,1,'') 'sizeid'
from [huang] a
group by a.[styleid]
----------------结果----------------------------
/*
styleid colorid sizeid
-------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AA125094 82,E5 L,M,S,XL
AA125601 71,77 L,M,S
*/
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'
;with ceb as
(
select styleid ,colorid ,
stuff((select ','+sizeid from #tb b where a.styleid=b.styleid and a.colorid=b.colorid for xml path('')),1,1,'') as sizeid
from #tb a group by styleid,colorid
)
select styleid,stuff((select ','+colorid from ceb b where a.styleid=b.styleid and a.sizeid=b.sizeid for xml path('')),1,1,'') as colorid,
sizeid
from ceb a group by styleid,sizeid
--styleid colorid sizeid
--AA125094 82，E5 L,M,S,XL
--AA125601 71,77 L,M,S
