行到列的SQL查询 [英] SQL Query For Row to Column

问题描述

请参阅以下SQL查询及其结果：

 选择 tbl.NetPayable，tbl。 OTAmount 来自（选择 sum（cast（S.NetPayable  as   decimal ）） as  NetPayable，sum（cast（S.OTAmount  as   decimal ）） as  OTAmount 来自 tblLocationWiseSalaryRecord S 
 其中​​月（S.SalarySTDT）= '  9' 和年份（S.SalarySTDT）= '   2013' 和 S.CompanyCode = '   CMP-001' 和 S.ETCode = '  ETC-001' 和 S.DeptCode = '  DPT-036'） as  tbl 
 
  Union   all  
（选择 sum（强制转换（S.NetPayable  as  < span class =code-keyword> decimal ）） as  NetPayable，sum（cast（S.OTAmount  as   decimal ）） as  OTAmount  From  tblLocationWiseSalaryRecord S 
 其中月（S.SalarySTDT）= '  9' 和年份（S.SalarySTDT）= '  2013' 和 S.CompanyCode = '  CMP-002' 和 S.ETCode = '  ETC-001' 和 S.DeptCode = '  DPT-036'）

 NetPayable OTAmount 
 207055 4073 
 201573 4610 

我想要的结果如下：

 NetPayable OTAmount NetPayable OTAmount 
 207055 4073 201573 4610 

我该怎么办？请帮帮我。

解决方案

你可以试试这个。我已更正错误

  CREATE   TABLE  #OutTable（NetPayable1  DECIMAL ，OTAmount1  DECIMAL ，NetPayable2  DECIMAL ，OTAmount2  DECIMAL ）
  DECLARE  < span class =code-sdkkeyword> @ NetPayable2   AS   DECIMAL  
 < span class =code-keyword> DECLARE   @ OTAmount2   AS   DECIMAL  
 
  INSERT   INTO  #OutTable （NetPayable1，OTAmount1）
 从选择 tbl.NetPayable，tbl.OTAmount （选择 sum（cast（S.NetPayable  as   decimal ）） as  NetPayable，sum（cast（S.OTAmount  as   decimal ）） as  OTAmount  From  tblLocationWiseSalaryRecord S 
 其中月（S.SalarySTDT）= '  9' 和 YEAR（S.SalarySTDT）= '  2013' 和 S.CompanyCode = '  CMP-001' 和 S.ETCode = '  ETC-001' 和 S.DeptCode = '  DP T-036'） as  tbl 
 
 
 
 选择  @ NetPayable2  = sum（cast（S.NetPayable  as   decimal ））， @ OTAmount2  = sum（cast（S.OTAmount  as   decimal ）） From  tblLocationWiseSalaryRecord S 
 其中月（S.SalarySTDT）= '  9' 和年份（S.SalarySTDT）= '  2013' 和 S.CompanyCode = '  CMP-002' 和 S.ETCode = '  ET C-001' 和 S.DeptCode = '  DPT-036' 
 更新 #OutTable  SET  NetPayable2 =  @ NetPayable2 ，OTAmount2 =  @ OTAmount2  
 
  SELECT  *  FROM  #OutTable 
  DROP  表 #OutTable 

请尝试以下操作：

http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx [ ^

Please see the following SQL query and its result :

Select tbl.NetPayable,tbl.OTAmount from (Select sum(cast(S.NetPayable as decimal))as NetPayable,sum (cast(S.OTAmount as decimal)) as OTAmount From tblLocationWiseSalaryRecord S
where month(S.SalarySTDT) = '9' and YEAR(S.SalarySTDT)='2013' and S.CompanyCode='CMP-001' and S.ETCode='ETC-001'and S.DeptCode='DPT-036')as tbl

Union all
(Select sum(cast(S.NetPayable as decimal))as NetPayable,sum (cast(S.OTAmount as decimal)) as OTAmount From tblLocationWiseSalaryRecord S
where month(S.SalarySTDT) = '9' and YEAR(S.SalarySTDT)='2013' and S.CompanyCode='CMP-002' and S.ETCode='ETC-001'and S.DeptCode='DPT-036')

NetPayable  OTAmount
207055       4073
201573       4610

I want the result like as follows :

NetPayable       OTAmount         NetPayable     OTAmount
207055           4073             201573         4610  

How can I do this. Please help me.

解决方案

You can try this. I have corrected the error

CREATE TABLE #OutTable( NetPayable1 DECIMAL, OTAmount1 DECIMAL, NetPayable2 DECIMAL, OTAmount2 DECIMAL)
DECLARE @NetPayable2 AS DECIMAL
DECLARE @OTAmount2 AS DECIMAL

INSERT INTO #OutTable (NetPayable1, OTAmount1)
Select tbl.NetPayable,tbl.OTAmount from (Select sum(cast(S.NetPayable as decimal))as NetPayable,sum (cast(S.OTAmount as decimal)) as OTAmount From tblLocationWiseSalaryRecord S
where month(S.SalarySTDT) = '9' and YEAR(S.SalarySTDT)='2013' and S.CompanyCode='CMP-001' and S.ETCode='ETC-001'and S.DeptCode='DPT-036')as tbl



Select @NetPayable2 = sum(cast(S.NetPayable as decimal)), @OTAmount2 = sum (cast(S.OTAmount as decimal)) From tblLocationWiseSalaryRecord S
where month(S.SalarySTDT) = '9' and YEAR(S.SalarySTDT)='2013' and S.CompanyCode='CMP-002' and S.ETCode='ETC-001'and S.DeptCode='DPT-036'
UPDATE #OutTable SET NetPayable2 = @NetPayable2, OTAmount2 = @OTAmount2

SELECT * FROM #OutTable
DROP TABLE #OutTable

Try below:
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx[^]

这篇关于行到列的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！