使用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屋！