请高手写个SQL语句

Question

OP_Serial          Soft_Bin Qty Description
10081000011 8 455 BG_REF_coded__Fail
10081000011 17 415 Margin_Check__Fail
10081000011 6 220 VDDon__Fail
10081000008 8 238 BG_REF_coded__Fail
10081000008 6 197 VDDon__Fail
10081000008 28 146 VCS_Imin__Fail
10081000014 10 1830 TRIM_ALL
10081000014 23 592 Iout_AC要得到如下，行边列，源数据同一个OP_Serial最多只有3行，所以转列，列数最多3列，每行的'['+Soft_Bin+']'+[Description]作为一列，Qty作为一列OP_Serial          FailTop1                   FailTop1Qty FailTop2       FailTop2Qty  FailTop3 FailTop3Qty
10081000011 [8]BG_REF_coded__Fail 455 [17]Margin_Check__Fail 415 [6]VDDon__Fail 220
10081000008 [8]BG_REF_coded__Fail 238 [6]VDDon__Fail 197 [28]VCS_Imin__Fail 146
10081000014 [10]TRIM_ALL 1830 [23]Iout_AC 592

Answer 1

OP_Serial   Soft_Bin Qty  Description
10081000011 8        455  BG_REF_coded__Fail
10081000011 17       415  Margin_Check__Fail
10081000011 6        220  VDDon__Fail
10081000008 8        238  BG_REF_coded__Fail
10081000008 6        197  VDDon__Fail
10081000008 28       146  VCS_Imin__Fail
10081000014 10       1830 TRIM_ALL
10081000014 23       592  Iout_AC

Answer 2

生产原始数据CREATE TABLE  TB (OP_Serial Nvarchar(50),Soft_Bin  Nvarchar(50),Qty  Nvarchar(50),Description  Nvarchar(50))INSERT INTO TB
SELECT'10081000011','8',455,'BG_REF_coded__Fail'UNION
SELECT'10081000011','17',415,'Margin_Check__Fail'UNION
SELECT'10081000011','6',220,'VDDon__Fail'UNION
SELECT'10081000008','8',238,'BG_REF_coded__Fail'UNION
SELECT'10081000008','6',197,'VDDon__Fail'UNION
SELECT'10081000008','28',146,'VCS_Imin__Fail'UNION
SELECT'10081000014','10',1830,'TRIM_ALL'UNION
SELECT'10081000014','23',592,'Iout_AC'查询代码：SELECT OP_Serial,   
MAX(CASE WHEN R_id%3=0 THEN '['+Soft_Bin+']'+[Description] ELSE NULL END) AS FailTop1,
MAX(CASE WHEN R_id%3=0 THEN Qty ELSE NULL END) AS FailTop1Qty,
MAX(CASE WHEN R_id%3=1 THEN '['+Soft_Bin+']'+[Description] ELSE NULL END) AS FailTop2,
MAX(CASE WHEN R_id%3=1 THEN Qty ELSE NULL END) AS FailTop2Qty,
MAX(CASE WHEN R_id%3=2 THEN '['+Soft_Bin+']'+[Description] ELSE NULL END) AS FailTop3,
MAX(CASE WHEN R_id%3=2 THEN Qty ELSE NULL END) AS FailTop3Qty
FROM (
     SELECT TOP 100 PERCENT *, R_id=(select count(*) from tb  as t1 where t1.OP_Serial=t2.OP_Serial and t1.Soft_Bin+t1.Qty+t1.Description<t2.Soft_Bin+t2.Qty+t2.Description)
     from TB as t2
     order by OP_Serial,t2.Soft_Bin+t2.Qty+t2.Description) T 
GROUP BY OP_Serial,R_id/3查询结果：
OP_Serial            FailTop1                    FailTop1Qty FailTop2                    FailTop2Qty FailTop3                    FailTop3Qty 
-------------------- --------------------------- ----------- --------------------------- ----------- --------------------------- ----------- 
10081000008          [28]VCS_Imin__Fail          146         [6]VDDon__Fail              197         [8]BG_REF_coded__Fail       238
10081000011          [17]Margin_Check__Fail      415         [6]VDDon__Fail              220         [8]BG_REF_coded__Fail       455
10081000014          [10]TRIM_ALL                1830        [23]Iout_AC                 592         NULL                        NULL（所影响的行数为 3 行）

Answer 3

测试代码都全给出了，哪里不解可以具体指出。
如果是用SQL 2005 , R_id 列可以直接用函数生成，本人用的2000

Answer 4

为什么要TOP 100 PERCENT 这个，麻烦能讲下吗？

Answer 5

因为有“order by ……” ，2000中嵌套子表排序需要TOP 100 PERCENT ，否则报错 
另，我不清楚OP_Serial之外有无唯一列，所有就用 Soft_Bin+Qty+Description 来排序了