如何在sql查询中获取表的字段作为列标题 [英] how to get fields of a table as column header in sql query>
本文介绍了如何在sql查询中获取表的字段作为列标题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设我有两个表:PAYTYPE和PAYDETAILS:
PAYTYPE具有类似PayTypeId,PayType
的字段
PAYDETAILS已归档诸如EmpCode,PayTypeId,Amount
的文件
现在,我想为每个PayType获取总计的单独列-雇员
例如:
EmpCode,Basic,HRA,其他津贴总计
解决方案
您需要使用PIVOTS
- 包含测试数据的临时表 DECLARE @ PayType TABLE (PayTypeId INT ,PayType VARCHAR ( 30 )) DECLARE @ PayDetails TABLE (EmpCode INT ,PayTypeId INT ,金额浮动) 插入 INTO @ PayType VALUES ( 1 ,' 插入 INTO @ PayType VALUES ( 2 ,' 插入 INTO @ PayType VALUES ( 3 ,' ) 插入 INTO @ PayDetails VALUES ( 1 , 1 , 12000') 插入 INTO @ PayDetails VALUES ( 1 , 2 , 6000') 插入 INTO @ PayDetails VALUES ( 1 , 3 , 3200') 插入 INTO @ PayDetails VALUES ( 2 , 1 , 5000') 插入 INTO @ PayDetails VALUES ( 2 , 2 , 3000') 插入 INTO @ PayDetails VALUES ( 2 , 3 , 1200') - 使用数据透视查询 SELECT EmpCode,[基本],[HRA],[其他津贴],([[基本] + [HRA] + [其他津贴])总计 FROM ( SELECT EmpCode,PayType,金额 FROM @ PayType pt 加入 @ PayDetails pd 打开 pt.PayTypeId = pd.PayTypeId) As 源 PIVOT(MAX(Amount) FOR PayType IN ([[Basic],[HRA],[Other Allowances] )) AS p
结果
EmpCode基本HRA其他津贴总计 - ----- ----- ----- --------- ------ ---- 1 12000 6000 21200 2 5000 3000 9200
了解有关PIVOTS的更多信息 http://msdn.microsoft.com/en-us/library/ms177410.aspx [ ^ ]
检查此
选择 * 来自 PAYTYPE,PAYDETAILS 1 = 解决方案
You need to use PIVOTS
--Temp tables with data for testing DECLARE @PayType TABLE ( PayTypeId INT, PayType VARCHAR(30)) DECLARE @PayDetails TABLE (EmpCode INT, PayTypeId INT, Amount Float) INSERT INTO @PayType VALUES (1, 'Basic') INSERT INTO @PayType VALUES (2, 'HRA') INSERT INTO @PayType VALUES (3, 'Other Allownces') INSERT INTO @PayDetails VALUES (1, 1, '12000') INSERT INTO @PayDetails VALUES (1, 2, '6000') INSERT INTO @PayDetails VALUES (1, 3, '3200') INSERT INTO @PayDetails VALUES (2, 1, '5000') INSERT INTO @PayDetails VALUES (2, 2, '3000') INSERT INTO @PayDetails VALUES (2, 3, '1200') --Query using Pivots SELECT EmpCode, [Basic], [HRA], [Other Allownces], ([Basic]+ [HRA]+[Other Allownces]) Total FROM (SELECT EmpCode, PayType, Amount FROM @PayType pt JOIN @PayDetails pd ON pt.PayTypeId = pd.PayTypeId) As Source PIVOT(MAX(Amount) FOR PayType IN ([Basic], [HRA], [Other Allownces]) )AS p
Result
EmpCode Basic HRA Other Allownces Total ------- ----- ----- --------------- ---- 1 12000 6000 3200 21200 2 5000 3000 1200 9200
Learn more about PIVOTS http://msdn.microsoft.com/en-us/library/ms177410.aspx[^]
Hi,
Check This Out
Select * from PAYTYPE , PAYDETAILS Where 1 = 2
Hope This Helps You...
这篇关于如何在sql查询中获取表的字段作为列标题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文