在SQL Server中，实现我想要的查询结果

[zwylly99]

/*
表结构：
BillNo   Attrib   EntryId
B001     1        E001
B001     1        E003
B001     1        E002
B001     1        E004
B001     2        I002
B001     2        I001
B001     2        I003

结果：
BillNo   EntryId_1    EntryId_2
B001     E001         I001
B001     E002         I002
B001     E003         I003
B001     E003         
*/
DECLARE @Test TABLE (BillNo VARCHAR(12), Attrib CHAR(1), EntryId VARCHAR(20))
INSERT INTO @Test
SELECT 'B001', '1', 'E001'
UNION ALL
SELECT 'B001', '1', 'E002'
UNION ALL
SELECT 'B001', '1', 'E004'
UNION ALL
SELECT 'B001', '1', 'E003'
UNION ALL
SELECT 'B001', '2', 'I002'
UNION ALL
SELECT 'B001', '2', 'I003'
UNION ALL
SELECT 'B001', '2', 'I001'

--怎么实现？

[liangCK]

DECLARE @Test TABLE (BillNo VARCHAR(12), Attrib CHAR(1), EntryId VARCHAR(20))
INSERT INTO @Test
SELECT 'B001', '1', 'E001'
UNION ALL
SELECT 'B001', '1', 'E002'
UNION ALL
SELECT 'B001', '1', 'E004'
UNION ALL
SELECT 'B001', '1', 'E003'
UNION ALL
SELECT 'B001', '2', 'I002'
UNION ALL
SELECT 'B001', '2', 'I003'
UNION ALL
SELECT 'B001', '2', 'I001'

--2000
select BillNo,
       MAX(case when Attrib=1 then EntryId else '' end) [EntryId_1],
       MAX(case when Attrib=2 then EntryId else '' end) [EntryId_2]
from 
(
   select *,px=(select COUNT(*) from @Test where BillNo=t.BillNo and Attrib=t.Attrib and EntryId<=t.EntryId)
   from @Test as t
) as t
group by BillNo,px

;
--2005
with cte as
(
  select *,ROW_NUMBER() over(partition by BillNo,Attrib order by EntryId) as px
  from @Test
)
select BillNo,
       MAX(case when Attrib=1 then EntryId else '' end) [EntryId_1],
       MAX(case when Attrib=2 then EntryId else '' end) [EntryId_2]
from cte
group by BillNo,px


/*
BillNo       EntryId_1            EntryId_2
------------ -------------------- --------------------
B001         E001                 I001
B001         E002                 I002
B001         E003                 I003
B001         E004                 

(4 行受影响)

*/