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

[ZXUN_XUE]

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

[super007007007]

select TD004,no1,no2 from
(select a.TD004,max(a.TD010)as no1,b.TD010 as no2 from
t1 as a
left join (select min(TD010) as TD010,TD004 from t1 group by TD004) as b
on a.TD004=b.TD004
group by a.TD004,b.TD010) as b
where no1<>no2

[super007007007]

select TD004,no1,no2 from
(select a.TD004,max(a.TD010)as no1,b.TD010 as no2 from
t1 as a
left join (select min(TD010) as TD010,TD004 from t1 group by TD004) as b
on a.TD004=b.TD004
group by a.TD004,b.TD010) as b
where no1<>no2

[liuhzdingxm]

seelct TD004,(select ','+TD010 from t1 as tB where tA.TD004=tB.TD004 for xml path('')) as Price from t1 as tA

[wylsjz]

declare @t1 table([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

select b.* from (select ROW_NUMBER() OVER(PARTITION BY td004 order by td010) id,* from @t1) a
pivot( min(td010) for a.id in ([1],[2]) ) b

[ZXUN_XUE]

上面语句运行不了哦

[ZXUN_XUE]

消息 325，级别 15，状态 1，第 2 行
'pivot' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。有关 ALTER DATABASE 的 SET COMPATIBILITY_LEVEL 选项的信息，请参见帮助。

[学习中-------]

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

[wylsjz]

select TD004,min(case when id=1 then TD010 end) '价格1',min(case when id=2 then TD010 end) '价格2' from (select ROW_NUMBER() OVER(PARTITION BY td004 order by td010) id,* from @t1) a
group by TD004

[---涛声依旧---]

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 } 参数 database_name 要修改的数据库的名称。 COMPATIBILITY_LEVEL { 80 | 90 | 100 } 要使数据库与之兼容的 SQL Server 版本。该值必须为下列值之一： 80 = SQL Server 2000 90 = SQL Server 2005 100 = SQL Server 2008