使用SQL pivot,数据无法相应地获得 [英] Using SQL pivot, data doesn't get accordingly
问题描述
因为我要执行sql查询,所以我没有相应的数据。让我告诉你一个例子
这是我的表结构
nId nCompanyId nEmpId sComp dAmt
1 1 101薪水50000
2 1 101 TDS 10000
3 1 101 PF 5000
4 1 201工资25000 >
5 1 201 TDS 6000
6 1 201 PF 2000
我想输出
nId nCompanyId nEmpId Salary TDS PF
1 1 101 50000 10000 5000
2 1 201 25000 6000 2000
我尝试过:
SELECT p。*
来自Form16_temp
PIVOT(
MAX([dAmt] )对于[sComp] IN([薪水],[TDS],[PF])
)为P
我的输出就像
nId nCompanyId nEmpId Salary TDS PF
1 1 101 50000 Null Null
2 1 101 Null 10000 Null
3 1 101 Null Null 5000
4 1 201 25000 Null Null
5 1 201 Null 6000 Null
6 1 201 Null Null 2000
As I'm going to perform sql query, I doesnt get data accordingly. Let me show you example
This my table structure
nId nCompanyId nEmpId sComp dAmt
1 1 101 Salary 50000
2 1 101 TDS 10000
3 1 101 PF 5000
4 1 201 Salary 25000
5 1 201 TDS 6000
6 1 201 PF 2000
I want output like
nId nCompanyId nEmpId Salary TDS PF
1 1 101 50000 10000 5000
2 1 201 25000 6000 2000
What I have tried:
SELECT p.*
FROM Form16_temp
PIVOT(
MAX([dAmt])
for [sComp] IN ([Salary],[TDS],[PF])
) as P
I got output like
nId nCompanyId nEmpId Salary TDS PF
1 1 101 50000 Null Null
2 1 101 Null 10000 Null
3 1 101 Null Null 5000
4 1 201 25000 Null Null
5 1 201 Null 6000 Null
6 1 201 Null Null 2000
推荐答案
试试这个查询
Try this query
SELECT nCompanyId, nEmpId, sum(Salary) Salary, sum(TDS) TDS,sum(PF) PF
FROM Form16_temp
PIVOT(
MAX([dAmt])
for [sComp] IN ([Salary],[TDS],[PF])
) as P
group by nCompanyId, nEmpId
Ashish Nigam
Ashish Nigam
这篇关于使用SQL pivot,数据无法相应地获得的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!