交叉表类型查询 [英] Cross Tab type Query

问题描述

我的SQL数据库中有一个表Employee master



Employeemaster表

I had a Table Employee master in my SQL Database

Employeemaster Table

Empid	EmpName
	
1	Emp1
2	Emp6
3	Emp4
4	Emp4

另一张表Employeesalarydetails

Another Table Employeesalarydetails

Empid	Component	Amount
		
2	Basic	10000
2	Hra	1000
2	TA	750
1	Basic	20000
1	Hra	1000
3	Basic	6700
3	Hra	1000
4	Basic	5000
4	Hra	1000

现在我想以一种形式创建一个查询，结果将是以

Now I want to Create a query in a way that result will be in form of will be in the form of

Empid	Empanme	 Basic	Hra	TA
1	Emp1	20000	1000	0
2	Emp6	10000	1000	750
3	Emp4	6700	1000	0
4	Emp4	5000	1000	0

我在Cross Query中尝试了所有可能的方法。任何人都可以建议我一个想法

I had tried all the possible methods in the Cross Query .Can anyone suggest me a idea

推荐答案

下面是一个动态列的示例参考（即。你不需要硬编码查询中的'组件'值）



示例数据#tempA，＃tempB

Below is a sample reference for dynamic columns (ie. u dont need to hard code 'Component' value in query)

Sample Data #tempA,#tempB

create Table #tempA (Empid bigint,EmpName VARCHAR(50))
INSERT INTO #tempA (Empid ,EmpName )VALUES(1,'Emp1')
INSERT INTO #tempA (Empid ,EmpName )VALUES(2,'Emp6')
INSERT INTO #tempA (Empid ,EmpName )VALUES(3,'Emp4')
INSERT INTO #tempA (Empid ,EmpName )VALUES(4,'Emp4')

create Table #tempB (id bigint ,Empid bigint,Component VARCHAR(50),Amount bigint)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(1,2,'Basic',10000)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(2,2,'Hra',1000)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(3,2,'TA',750)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(4,1,'Basic',20000)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(5,1,'Hra',1000)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(6,3,'Basic',6700)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(7,3,'Hra',1000)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(8,4,'Basic',5000)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(9,4,'Hra',1000)

结合两个表格#tempc

Combine Two Tables into #tempc

SELECT id,#tempB.Empid,EmpName,Component,Amount INTO #tempc
FROM #tempB
INNER JOIN #tempA ON #tempA.Empid=#tempB.Empid

声明变量

Declare variables

DECLARE @cols NVARCHAR (MAX),
@selCols NVARCHAR (MAX),
@query  AS VARCHAR(MAX);

获取动态查询的列，如[基本]，[Hra]，[TA]

Fetch columns for dynamic query like [Basic],[Hra],[TA]

SELECT  @cols = COALESCE (@cols + ',[' + Component + ']', '[' + Component + ']') 
FROM    #tempB
group by Component

Fetch动态查询中的别名列，如[基本] AS Basic，[Hra] AS Hra，[TA] AS TA

Fetch columns for Aliasing in dynamic query like [Basic] AS Basic,[Hra] AS Hra,[TA] AS TA

SELECT  @selCols = COALESCE (@selCols + ',[' + Component + ']', '[' + Component + ']') + ' AS '+ Component
FROM    #tempB
group by Component

在动态中获取SUM的列查询如SUM（isnull（Basic，0））AS Basic，SUM（isnull（Hra，0））AS Hra，SUM（isnull（TA，0））AS TA

Fetch columns for SUM in dynamic query like SUM(isnull(Basic,0)) AS Basic,SUM(isnull(Hra,0)) AS Hra,SUM(isnull(TA,0)) AS TA

SET @selCols=REPLACE(@selCols,'[','SUM(isnull(')
SET @selCols=REPLACE(@selCols,']',',0))')

最终查询

Final Query

SET @query='SELECT Empid,EmpName,'+ @selCols+
		' FROM #tempc 
		PIVOT(
				SUM(Amount) 
				FOR Component 
				IN('+@cols+')		
				
		)AS empPivot
		group by Empid,EmpName'
execute(@query)		

祝你好运; - ）

good luck ;-)

您应该在SQL Server中使用 Pivot 方法。

阅读我的文章 [ ^ ]关于如何这样做。

You should use the Pivot methods in SQL Server.
Read my article[^] about how to do so.

检查这个... < br $> b $ b

在SQL查询中使用Pivot的简单方法 [ ^ ]



...用于EmployeeSalaryDetails表。



我想再给你一次机会尝试自己获得所需的输出。



希望这会对你有帮助。





干杯。

Hi,

Check this...

Simple Way To Use Pivot In SQL Query[^]

... for EmployeeSalaryDetails table.

I will like to give you one more chance to try by your own to get required output.

Hope this will help you.


Cheers.

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