要查询的表
select qid,cid,pid,QuotePrice from quote执行结果
qid cid pid QuotePrice
----------- ----------- ----------- -----------------------------------------------------
3 3 6 500.0
4 2 7 1000.0
5 3 8 1500.0
6 1 6 1000.0
7 2 6 8000.0
8 1 7 500.0
10 1 8 1000.0
11 2 8 5000.0
12 2 11 500.0
13 1 11 1000.0
14 3 11 1500.0
15 1 10 500.0
16 1 12 500.0
17 4 6 1111.0
18 4 7 1111.0
19 2 10 131.0
20 3 10 123.0（所影响的行数为 17 行）行专列代码
select qID,cid,pid,
max(case px when 1 then cid else '' end) 'comName1',
max(case px when 1 then QuotePrice else '' end) 'Price1',
max(case px when 2 then cid else '' end) 'comName2',
max(case px when 2 then QuotePrice else '' end) 'Price2',
max(case px when 3 then cid else '' end) 'comName3',
max(case px when 3 then QuotePrice else '' end) 'Price3'
from (select top 1000 px=(select count(1) from quote where pid=a.pid and QuotePrice<a.QuotePrice)+1 ,* from quote as a order by pid,QuotePrice) as t
group by qid,cid,pid,QuotePrice执行结果
qID cid pid comName1 Price1 comName2 Price2 comName3 Price3
--- ---- ---- -------- ------ -------- ------- -------- -------
3 3 6 3 500.0 0 0.0 0 0.0
6 1 6 0 0.0 1 1000.0 0 0.0
17 4 6 0 0.0 0 0.0 4 1111.0
7 2 6 0 0.0 0 0.0 0 0.0
8 1 7 1 500.0 0 0.0 0 0.0
4 2 7 0 0.0 2 1000.0 0 0.0
18 4 7 0 0.0 0 0.0 4 1111.0
10 1 8 1 1000.0 0 0.0 0 0.0
5 3 8 0 0.0 3 1500.0 0 0.0
11 2 8 0 0.0 0 0.0 2 5000.0
20 3 10 3 123.0 0 0.0 0 0.0
19 2 10 0 0.0 2 131.0 0 0.0
15 1 10 0 0.0 0 0.0 1 500.0
12 2 11 2 500.0 0 0.0 0 0.0
13 1 11 0 0.0 1 1000.0 0 0.0
14 3 11 0 0.0 0 0.0 3 1500.0
16 1 12 1 500.0 0 0.0 0 0.0
（所影响的行数为 17 行）上面记录，pid相同的记录还是分行显示，原来价格和cid匹配的记录，也对应到新的列中，但就不是一行，请问是那里出错了啊？
select qid,cid,pid,QuotePrice from quote执行结果
qid cid pid QuotePrice
----------- ----------- ----------- -----------------------------------------------------
3 3 6 500.0
4 2 7 1000.0
5 3 8 1500.0
6 1 6 1000.0
7 2 6 8000.0
8 1 7 500.0
10 1 8 1000.0
11 2 8 5000.0
12 2 11 500.0
13 1 11 1000.0
14 3 11 1500.0
15 1 10 500.0
16 1 12 500.0
17 4 6 1111.0
18 4 7 1111.0
19 2 10 131.0
20 3 10 123.0（所影响的行数为 17 行）行专列代码
select qID,cid,pid,
max(case px when 1 then cid else '' end) 'comName1',
max(case px when 1 then QuotePrice else '' end) 'Price1',
max(case px when 2 then cid else '' end) 'comName2',
max(case px when 2 then QuotePrice else '' end) 'Price2',
max(case px when 3 then cid else '' end) 'comName3',
max(case px when 3 then QuotePrice else '' end) 'Price3'
from (select top 1000 px=(select count(1) from quote where pid=a.pid and QuotePrice<a.QuotePrice)+1 ,* from quote as a order by pid,QuotePrice) as t
group by qid,cid,pid,QuotePrice执行结果
qID cid pid comName1 Price1 comName2 Price2 comName3 Price3
--- ---- ---- -------- ------ -------- ------- -------- -------
3 3 6 3 500.0 0 0.0 0 0.0
6 1 6 0 0.0 1 1000.0 0 0.0
17 4 6 0 0.0 0 0.0 4 1111.0
7 2 6 0 0.0 0 0.0 0 0.0
8 1 7 1 500.0 0 0.0 0 0.0
4 2 7 0 0.0 2 1000.0 0 0.0
18 4 7 0 0.0 0 0.0 4 1111.0
10 1 8 1 1000.0 0 0.0 0 0.0
5 3 8 0 0.0 3 1500.0 0 0.0
11 2 8 0 0.0 0 0.0 2 5000.0
20 3 10 3 123.0 0 0.0 0 0.0
19 2 10 0 0.0 2 131.0 0 0.0
15 1 10 0 0.0 0 0.0 1 500.0
12 2 11 2 500.0 0 0.0 0 0.0
13 1 11 0 0.0 1 1000.0 0 0.0
14 3 11 0 0.0 0 0.0 3 1500.0
16 1 12 1 500.0 0 0.0 0 0.0
（所影响的行数为 17 行）上面记录，pid相同的记录还是分行显示，原来价格和cid匹配的记录，也对应到新的列中，但就不是一行，请问是那里出错了啊？
--因为你的QID是不重复的 你GROUP BY QID 就会全部显示出来
--你这样试一下
select max(qID),cid,pid,
max(case px when 1 then cid else '' end) 'comName1',
max(case px when 1 then QuotePrice else '' end) 'Price1',
max(case px when 2 then cid else '' end) 'comName2',
max(case px when 2 then QuotePrice else '' end) 'Price2',
max(case px when 3 then cid else '' end) 'comName3',
max(case px when 3 then QuotePrice else '' end) 'Price3'
from (select top 1000 px=(select count(1) from quote where pid=a.pid and QuotePrice<a.QuotePrice)+1 ,* from quote as a order by pid,QuotePrice) as t
group by cid,pid
cid pid comName1 Price1 comName2 Price2 comName3 Price3
----------- ----------- ----------- ----------- ----------------------------------------------------- ----------- ----------------------------------------------------- ----------- -----------------------------------------------------
6 1 6 0 0.0 1 1000.0 0 0.0
8 1 7 1 500.0 0 0.0 0 0.0
10 1 8 1 1000.0 0 0.0 0 0.0
15 1 10 0 0.0 0 0.0 1 500.0
13 1 11 0 0.0 1 1000.0 0 0.0
16 1 12 1 500.0 0 0.0 0 0.0
7 2 6 0 0.0 0 0.0 0 0.0
4 2 7 0 0.0 2 1000.0 0 0.0
11 2 8 0 0.0 0 0.0 2 5000.0
19 2 10 0 0.0 2 131.0 0 0.0
12 2 11 2 500.0 0 0.0 0 0.0
3 3 6 3 500.0 0 0.0 0 0.0
5 3 8 0 0.0 3 1500.0 0 0.0
20 3 10 3 123.0 0 0.0 0 0.0
14 3 11 0 0.0 0 0.0 3 1500.0
17 4 6 0 0.0 0 0.0 4 1111.0
18 4 7 0 0.0 0 0.0 4 1111.0（所影响的行数为 17 行）
结果是
cid pid comName1 Price1 comName2 Price2 comName3 Price3
----------- ----------- ----------- ----------- ----------------------------------------------------- ----------- ----------------------------------------------------- ----------- -----------------------------------------------------
6 1 6 0 0.0 1 1000.0 0 0.0
7 2 6 0 0.0 0 0.0 0 0.0
3 3 6 3 500.0 0 0.0 0 0.0
17 4 6 0 0.0 0 0.0 4 1111.0
8 1 7 1 500.0 0 0.0 0 0.0
4 2 7 0 0.0 2 1000.0 0 0.0
18 4 7 0 0.0 0 0.0 4 1111.0
10 1 8 1 1000.0 0 0.0 0 0.0
11 2 8 0 0.0 0 0.0 2 5000.0
5 3 8 0 0.0 3 1500.0 0 0.0
15 1 10 0 0.0 0 0.0 1 500.0
19 2 10 0 0.0 2 131.0 0 0.0
20 3 10 3 123.0 0 0.0 0 0.0
13 1 11 0 0.0 1 1000.0 0 0.0
12 2 11 2 500.0 0 0.0 0 0.0
14 3 11 0 0.0 0 0.0 3 1500.0
16 1 12 1 500.0 0 0.0 0 0.0（所影响的行数为 17 行）
6 1 6 0 0.0 1 1000.0 0 0.0
7 2 6 0 0.0 0 0.0 0 0.0
3 3 6 3 500.0 0 0.0 0 0.0
17 4 6 0 0.0 0 0.0 4 1111.0显示还是错误，结果应该是pid comName1 Price1 comName2 Price2 comName3 Price3
----------------------------------------------------- ------------------------------------
6 3 500 1 1000 4 1111上面的转列语句我把这个结果显示列外的字段都去掉了，select的和group by的，但是结果还是错误。不知道是那里错了 =。=
insert into @t
select 3,3,6,500
union all select 4, 2, 7, 1000.0
union all select 5, 3 , 8, 1500.0
union all select 6, 1, 6, 1000.0
union all select 7, 2, 6, 8000.0
union all select 8, 1, 7, 500.0
union all select 10, 1, 8, 1000.0
union all select 11, 2, 8, 5000.0
union all select 12, 2, 11, 500.0
union all select 13, 1, 11, 1000.0
union all select 14, 3, 11, 1500.0
union all select 15, 1, 10, 500.0
union all select 16, 1, 12, 500.0
union all select 17, 4, 6, 1111.0
union all select 18, 4, 7, 1111.0
union all select 19, 2, 10, 131.0
union all select 20, 3, 10, 123.0
select max(qID),cid,
max(case px when 1 then qid else '' end) 'qid1',
max(case px when 1 then QuotePrice else '' end) 'Price1',
max(case px when 2 then qid else '' end) 'qid2',
max(case px when 2 then QuotePrice else '' end) 'Price2',
max(case px when 3 then qid else '' end) 'qid3',
max(case px when 3 then QuotePrice else '' end) 'Price3',
max(case px when 4 then qid else '' end) 'qid4',
max(case px when 4 then QuotePrice else '' end) 'Price4'
from (select top 1000 px=(select count(1) from @t where pid=a.pid and QuotePrice<a.QuotePrice)+1 ,* from @t as a order by pid,QuotePrice) as t
group by cid
目的是 将同pid（产品id）的信息合并到一起
主要合并的字段是cid(供应商id)和QuotePrice（报价）我做的是报价的功能，这个页面要显示一个产品报价的最低的3个供应商和它对应的价格……表quote就是存的报价的相关信息
--哦是这样的啊declare @t table(qid int,cid int,pid int,quoteprice float)
insert into @t
select 3,3,6,500
union all select 4, 2, 7, 1000.0
union all select 5, 3 , 8, 1500.0
union all select 6, 1, 6, 1000.0
union all select 7, 2, 6, 8000.0
union all select 8, 1, 7, 500.0
union all select 10, 1, 8, 1000.0
union all select 11, 2, 8, 5000.0
union all select 12, 2, 11, 500.0
union all select 13, 1, 11, 1000.0
union all select 14, 3, 11, 1500.0
union all select 15, 1, 10, 500.0
union all select 16, 1, 12, 500.0
union all select 17, 4, 6, 1111.0
union all select 18, 4, 7, 1111.0
union all select 19, 2, 10, 131.0
union all select 20, 3, 10, 123.0select max(qID),
max(case px when 1 then cid else '' end) 'comName1',
max(case px when 1 then pid else '' end) 'pid1',
max(case px when 1 then QuotePrice else '' end) 'Price1',
max(case px when 2 then cid else '' end) 'comName2',
max(case px when 2 then pid else '' end) 'pid2',
max(case px when 2 then QuotePrice else '' end) 'Price2',
max(case px when 3 then cid else '' end) 'comName3',
max(case px when 3 then pid else '' end) 'pid3',
max(case px when 3 then QuotePrice else '' end) 'Price3'
from (select px=(select count(1) from @t where pid=a.pid and quoteprice<a.quoteprice )+1 ,* from @t as a ) as t
group by pid
--------------------------------------------
不过发现另外一个问题，如果一个产品有相同价格，并且是在最低的3个里面，就会有问题。
如同一个产品，第一个低价500，第二个低价500，第三个低价600 最后输出结果就是 第二个cid pid 和QuotePrice 都是0的情况……这个好像是
select px=(select count(1) from @t where pid=a.pid and quoteprice<a.quoteprice )+1 ,* from @t as a
的问题
相同的2个价格 px的值会出现
1
1
这样重复的
--不好意思 昨天下午出去了 晚上没有回来 刚刚才上线 你的问题这样解决
--这样借助临时表生成一个自增长的列 用它去生成 PX 值这样就会全部显示出来了
select id=identity(int,1,1),* into # from @t order by pid,quoteprice,cidselect max(qID),
max(case px when 1 then cid else '' end) 'comName1',
max(case px when 1 then pid else '' end) 'pid1',
max(case px when 1 then QuotePrice else '' end) 'Price1',
max(case px when 2 then cid else '' end) 'comName2',
max(case px when 2 then pid else '' end) 'pid2',
max(case px when 2 then QuotePrice else '' end) 'Price2',
max(case px when 3 then cid else '' end) 'comName3',
max(case px when 3 then pid else '' end) 'pid3',
max(case px when 3 then QuotePrice else '' end) 'Price3'
from (select px=(select count(1) from # where pid=a.pid and id<a.id )+1 ,* from # as a ) as t--在这里改一下用ID代替quoteprice
group by pid