ÇóÒ»¸öSQL²éѯ - MS-SQL Server / Ó¦ÓÃʵÀý

±íA
×Ö¶Îa ×Ö¶Îb
01 a
02 b
03 c
04 d

±íB
×Ö¶Îa ×Ö¶Îc ×Ö¶ÎX  
01 a001 1
01 a002 2
01 a003 3
02 b001 1
02 b002 2
02 b003 3
03 c001 1
03 c002 2
03 c003 3
04 d001 1
04 d002 2
04 d003 3

Çó²éѯ½á¹ûΪ
01 a a001 a002 a003
02 b b001 b002 b003
03 c c001 c002 c003
04 d d001 d002 d003

sql ²éѯ´úÂëÈçºÎд лл~

---

×Ö¶ÎX ¹Ì¶¨ÊÇ3¸öÂð£¿

---

select a.*,
  max(case b.x when 1 then b.c else '' end) [1],
  max(case b.x when 2 then b.c else '' end) [2],
  max(case b.x when 3 then b.c else '' end) [3]
from a , b
where a.a = b.a 
group by a.a a.b

---

select a.*,
  max(case b.x when 1 then b.c else '' end) [1],
  max(case b.x when 2 then b.c else '' end) [2],
  max(case b.x when 3 then b.c else '' end) [3]
from a , b
where a.a = b.a 
group by a.a ,a.b

---

SQL code:
----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-05-19 15:50:56
-- Version: Microsoft SQL Server 2008 (RTM) -