我有以下表结构&以下是我正在执行的查询:-
下表是:-
第一表:
第二张表:-
SELECT Code, Fname, OTHINC1, OTHINC2
FROM (SELECT a.FieldName
,a.YearlyValue
,b.Code
,b.Fname
FROM [TaxOtherIncome] AS a
INNER JOIN [EmployeeDetail] AS b
ON a.EmployeeId = b.Id
WHERE a.EntryDate = '2014-12-01') x
PIVOT (MAX(YearlyValue) FOR FieldName IN (OTHINC1,OTHINC2)) p
结果我得到了:-
我也想要上面提到的第二个表中的记录。因此,将在我的最终结果列中添加SUBFLD36
SUBFLD37
&House_Rent
。
由于我是Pivot的新手,请帮助我修改以上查询以获取预期的结果。
您可以尝试以下方法:
SELECT Code, Fname, OTHINC1, OTHINC2, SUBFLD36, SUBFLD37, House_Rent
FROM (SELECT a.FieldName
,a.YearlyValue
,b.Code
,b.Fname
FROM [TaxOtherIncome] AS a
INNER JOIN [EmployeeDetail] AS b
ON a.EmployeeId = b.Id
WHERE a.EntryDate = '2014-12-01'
UNION
SELECT a.FieldName
,a.FieldValue AS YearlyValue
,a.EmployeeCode AS Code
,b.Fname
FROM SecondTable AS a
INNER JOIN [EmployeeDetail] AS b
ON a.EmployeeId = b.Id
WHERE EntryDate = '2014-12-01') x
PIVOT (MAX(YearlyValue) FOR FieldName IN (OTHINC1, OTHINC2, SUBFLD36, SUBFLD37, House_Rent)) p
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句