用SQL实现只显示同一个品号有两个不同价格的解决方法

用SQL实现只显示同一个品号有两个不同价格的


create table [t1]([TD004] varchar(11),[TD010] numeric(5,2))
insert [t1]
select '3INKSX0636M',270.00 union all 
select '3INKSX0636M',280.00 union all 
select '3INKSX0004M',270.00 union all
select '3INKBTT307W',480.00 union all
select '3INKBTL0288',160.00 union all
select '3INKBTT307W',480.00 union all
select '3INKBT0229D',230.00 union all
select '3INKBT0229D',220.00

显示效果:
3INKSX0636M 270.00, 280.00
3INKBT0229D 220.00, 230.00

------解决方案--------------------

SQL code

with a as (select td004,td010 from t1 where td004  in ( select td004 from t1 group by td004  having count(distinct td010)=2) )select distinct td004,td010=stuff((select ','+ cast(td010 as varchar(max)) from a  where a.td004=b.td004 for xml path ('') ),1,1,'') from a b