有2个表 一个表是H_Order，一个表是H_Form
字段分别如图：
我是用这段代码得出的结果是
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
就2行而已,这样的代码怎么写？？？？
创建2个表的SQL代码如下
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')
------解决思路----------------------
你把formNa字段为空的数据排除掉，不就是你想要的格式了
------解决思路----------------------
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
------解决思路----------------------
你都写出来了，直接聚合就可以了
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