SQL中的复杂数据透视表 [英] Complex Pivot table in sql

问题描述

大家好，

我在使用存储过程的sql中有一个非常复杂的要求，我有三个表，分别是-zone_master，complaint_master，status_master.

zone_master具有区域名称及其ID:

Hi everyone,

I have one very complex requirement in sql using storedprocedures, i have three tables namely -zone_master,complaint_master,status_master.

zone_master has names of zone and its id :

zoneid	zonename
1	North
2	South
3	West
4	East

status_master中有投诉状态

status_master has complaint status in it

statusid	statusname
1	pending
2	closed
3	noreply
4	partly closed

投诉主控中有投诉

complaint_master has complaints in it

complaintid	complaintnumber	complaintstatusid	zoneid
1	mx/hw/123	1	1
2	mx/sw/234	2	2
3	mx/sw/235	3	3
4	mx/sw/236	4	4

现在我需要使用存储过程在sql中作为非常复杂的数据透视表输出...

输出应为:

now what i need in output as a very complex pivot table in sql using stored procedures...

output should be :

Zone	PendingCount	ClosedCount	NoreplyCount	PartlyClosedCound	GrandTotal
North	1	1	1	1	4
south	1	1	1	1	4
west	1	1	1	1	4
east	1	1	1	1	4

请帮助我.....

谢谢&问候，
克鲁纳尔·潘查尔


已添加代码标签-LOSMAC [/EDIT]

Please help me how it is possible .....

Thanks & Regards,
Krunal Panchal


Code tags added - LOSMAC[/EDIT]

推荐答案

检查此内容:

Check this:

--if temporary tables exists, drop tables
IF NOT OBJECT_ID('#zone_master','U') IS NULL
	DROP TABLE #zone_master
IF NOT OBJECT_ID('#status_master','U') IS NULL
	DROP TABLE #status_master
IF NOT OBJECT_ID('#complaint_master','U') IS NULL
	DROP TABLE #complaint_master

--create temporary tables and insert values
CREATE TABLE #zone_master(zoneid INT, zonename VARCHAR(30))
INSERT INTO #zone_master(zoneid, zonename)
	VALUES(1, 'North')
INSERT INTO #zone_master(zoneid, zonename)
	VALUES(2, 'South')
INSERT INTO #zone_master(zoneid, zonename)
	VALUES(3, 'West')
INSERT INTO #zone_master(zoneid, zonename)
	VALUES(4, 'East')

CREATE TABLE #status_master (statusid INT,  statusname VARCHAR(30))
INSERT INTO #status_master (statusid, statusname)
	VALUES(1, 'pending')
INSERT INTO #status_master (statusid, statusname)
	VALUES(2, 'closed')
INSERT INTO #status_master (statusid, statusname)
	VALUES(3, 'noreply')
INSERT INTO #status_master (statusid, statusname)
	VALUES(4, 'partly closed')

CREATE TABLE #complaint_master(complaintid	INT, complaintnumber VARCHAR(30), complaintstatusid INT, zoneid INT)
INSERT INTO #complaint_master(complaintid, complaintnumber, complaintstatusid, zoneid)
	VALUES(1, 'mx/hw/123', 1,	1)
INSERT INTO #complaint_master(complaintid, complaintnumber, complaintstatusid, zoneid)
	VALUES(2, 'mx/sw/234', 2,	2)
INSERT INTO #complaint_master(complaintid, complaintnumber, complaintstatusid, zoneid)
	VALUES(3, 'mx/sw/235', 3,	3)
INSERT INTO #complaint_master(complaintid, complaintnumber, complaintstatusid, zoneid)
	VALUES(4, 'mx/sw/236', 4,	4)

--declare variables
DECLARE @cols VARCHAR(200)
DECLARE @cols1 VARCHAR(200)
DECLARE @dt VARCHAR(1000)
DECLARE @pt VARCHAR(2000)
DECLARE @fq VARCHAR(4000)

--set variables
--cols -> get all statusname separated by " , "
SET @cols = STUFF((SELECT '],[' + [statusname]
					FROM #status_master
					ORDER BY '],[' + CONVERT(VARCHAR(5),[statusid])
			FOR XML PATH('')),1,2,'') + ']'
--cols1 -> get all statusname separated by " + "
SET @cols1 = STUFF((SELECT '] + [' + [statusname]
					FROM #status_master
					ORDER BY '] + [' + CONVERT(VARCHAR(5),[statusid])
			FOR XML PATH('')),1,2,'') + ']'
--get non-pivoted data
SET @dt = 'SELECT  zm.zonename, sm.statusname ' +
			'FROM #complaint_master AS cm ' +
				'LEFT JOIN #zone_master AS zm ON cm.zoneid = zm.zoneid ' +
				'LEFT JOIN #status_master AS sm ON cm.complaintstatusid = sm.statusid '
--EXEC(@dt)

--get pivoted data
SET @pt = 'SELECT zonename, ' + @cols + ' ' +
			'FROM(' + @dt + ') AS DT ' + 
			'PIVOT (COUNT(statusname) FOR statusname IN(' + @cols + ')) AS PT ' 
--EXEC(@pt)

--remove " + "
SET @cols1 = SUBSTRING(@cols1,3,LEN(@cols1)-2)

--get pivoted data with grand total
SET @fq = 'SELECT zonename, ' + @cols + ', (' + @cols1 +') AS GrandTotal ' +
	'FROM (' + @pt + ') AS FQ'
EXEC (@fq)

--remove temporary tables
DROP TABLE #complaint_master
DROP TABLE #zone_master
DROP TABLE #status_master

这篇关于SQL中的复杂数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！