27,582
社区成员




select a.djbh 单号,
case b.formNa when 'found' then b.formval else '' end 经费来源,
case b.formNa when 'national' then b.formval else '' end 国籍,
case b.formNa when 'passno' then b.formval else '' end 护照
from dbo.H_Order a,dbo.H_Form b where a.apid=b.apid
单号 经费来源 国籍 护照
10001 自费 卡塔尔 68985
10002 奖学金 美国 898979
if exists (select * from sysobjects where id = OBJECT_ID('[H_Form]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [H_Form]
CREATE TABLE [H_Form] (
[id] [int] IDENTITY (1, 1) NOT NULL,
[formval] [nvarchar] (20) NULL,
[formNa] [nvarchar] (20) NULL,
[apid] [int] NULL)
SET IDENTITY_INSERT [H_Form] ON
INSERT [H_Form] ([id],[formval],[apid]) VALUES ( 1,N'aa',1)
INSERT [H_Form] ([id],[formval],[formNa],[apid]) VALUES ( 2,N'自费',N'found',1)
INSERT [H_Form] ([id],[formval],[apid]) VALUES ( 3,N'bb',1)
INSERT [H_Form] ([id],[formval],[formNa],[apid]) VALUES ( 4,N'卡塔尔',N'national',1)
INSERT [H_Form] ([id],[formval],[apid]) VALUES ( 5,N'bb',1)
INSERT [H_Form] ([id],[formval],[formNa],[apid]) VALUES ( 6,N'68985',N'passno',1)
INSERT [H_Form] ([id],[formval],[formNa],[apid]) VALUES ( 7,N'奖学金',N'found',2)
INSERT [H_Form] ([id],[formval],[apid]) VALUES ( 8,N'b',2)
INSERT [H_Form] ([id],[formval],[formNa],[apid]) VALUES ( 9,N'美国',N'national',2)
INSERT [H_Form] ([id],[formval],[apid]) VALUES ( 10,N'aaa',2)
INSERT [H_Form] ([id],[formval],[formNa],[apid]) VALUES ( 11,N'898979',N'passno',2)
SET IDENTITY_INSERT [H_Form] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[H_Order]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [H_Order]
CREATE TABLE [H_Order] (
[apid] [int] NULL,
[djbh] [nvarchar] (20) NULL)
INSERT [H_Order] ([apid],[djbh]) VALUES ( 1,N'10001')
INSERT [H_Order] ([apid],[djbh]) VALUES ( 2,N'10002')
INSERT [H_Order] ([apid],[djbh]) VALUES ( 3,N'10003')
select a.djbh 单号,
MAX(case b.formNa when 'found' then b.formval else '' END) 经费来源,
MAX(case b.formNa when 'national' then b.formval else '' END) 国籍,
MAX(case b.formNa when 'passno' then b.formval else '' END) 护照
from dbo.H_Order a,dbo.H_Form b where a.apid=b.apid
GROUP BY a.djbh
SELECT a.djbh 单号,
b1.经费来源,
b2.国籍,
b3.护照
FROM H_Order a
JOIN (SELECT apid, formval 经费来源 FROM H_Form WHERE formNa = 'found') b1
ON a.apid = b1.apid
JOIN (SELECT apid, formval 国籍 FROM H_Form WHERE formNa = 'national') b2
ON a.apid = b2.apid
JOIN (SELECT apid, formval 护照 FROM H_Form WHERE formNa = 'passno') b3
ON a.apid = b3.apid
单号 经费来源 国籍 护照
----- -------- ------ ------
10001 自费 卡塔尔 68985
10002 奖学金 美国 898979