存储过程中的数据透视表 [英] pivot tables in stored procedure

问题描述

你好，

我在SQL中有一个存储过程，我想在删除tha表之前创建一个数据透视表.

如何创建数据透视表，如下所示:
前三列:
RequestID |状态|签名| RequestDateStart |

在前三列之后的右侧，具有以下标题:
SFA否|费用£|估算费用|费用€|实际费用|原因代码

然后将在标题下填充值.

这是所有数据的存储位置:

RequestID-dbo.WFPROCESS_INST(作为ID_Process_Inst)
状态-dbo.WFPROCESS_INST(作为ID_State)
签名-dbo.USER(作为Extatt_1)
日期开始-dbo.WFPROCESS_INST(作为日期开始)

SFA编号-dbo.WFDATASET_VALUE(作为Specf_sfa_no_txtbox)
费用£-dbo.WFDATASET_VALUE(按Speci_dropdown)
预估费用-dbo.WFDATASET_VALUE(按Spec_Estimated_cost)
费用€-dbo.WFDATASET_VALUE(为CCV_dropdown)
实际费用-dbo.WFDATASET_VALUE(为CCV_Actual_Cost)
原因代码-dbo.WFDATASET_VALUE(按Spec_Reason)

这是我的代码如下:

Hi there

I have a stored procedure in SQL and i want to create a pivot table just before i drop tha table.

How do i create a pivot table to look like this:
First three columns:
RequestID|State|Signature|RequestDateStart|

on the right after the first three columns to have following headers:
SFA No|Cost £|Eastimated Cost|cost €|Actual Cost|Reason Code

then values will be populated under the headers.

here is where all the data is stored:

RequestID - dbo.WFPROCESS_INST (As ID_Process_Inst)
State - dbo.WFPROCESS_INST (As ID_State)
Signature - dbo.USER (As Extatt_1)
Date Start - dbo.WFPROCESS_INST (As Date_Start)

SFA No - dbo.WFDATASET_VALUE (As Specf_sfa_no_txtbox)
Cost £ - dbo.WFDATASET_VALUE (As Speci_dropdown)
Eastimated Cost - dbo.WFDATASET_VALUE (As Spec_Estimated_cost)
Cost € - dbo.WFDATASET_VALUE (As CCV_dropdown)
Actual Cost - dbo.WFDATASET_VALUE (As CCV_Actual_Cost)
Reason Code - dbo.WFDATASET_VALUE (As Spec_Reason)

Here is my code below:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SFAPutData]
AS
	CREATE TABLE #Test
	(
		RequestID INT,
		RequestName NVARCHAR(64),
		RequestDescription NVARCHAR(255),
		RequestDateStart DATETIME,
		State NVARCHAR(20),
		ValueText NVARCHAR(4000),
		Signature NVARCHAR(255),
		Data_Value NVARCHAR(64)
	)
	INSERT INTO #Test
		(RequestID, RequestName, RequestDescription, RequestDateStart, State, ValueText, Signature, Data_Value)
	SELECT
			dbo.WFPROCESS_INST.ID_PROCESS_INST AS REQUEST_ID
			, dbo.WFPROCESS_INST.NAME AS REQUEST_NAME
			, dbo.WFPROCESS_INST.DESCRIPTION AS REQUEST_DESCRIPTION
			, dbo.WFPROCESS_INST.DATE_START AS REQUEST_DATE_START
			, dbo.WFPROCESS_INST.ID_STATE AS PROCESS_REQUEST_XXXX
			, dbo.WFDATASET_VALUE.VALUE_TEXT AS DATA_VALUE_TEXT
			, dbo.USERS.EXTATT_1
			, dbo.WFRELDATA.NAME AS DATA_VALUE_NAME
	 FROM dbo.WFPROCESS_INST
	INNER JOIN dbo.WFPROCESS ON dbo.WFPROCESS_INST.ID_PROCESS = dbo.WFPROCESS.ID_PROCESS 
	INNER JOIN dbo.WFPROCESS_INST_RELDATA ON dbo.WFPROCESS_INST.ID_PROCESS_INST = dbo.WFPROCESS_INST_RELDATA.ID_PROCESS_INST 
	INNER JOIN dbo.WFRELDATA ON dbo.WFPROCESS_INST_RELDATA.ID_RELDATA = dbo.WFRELDATA.ID_RELDATA 
	INNER JOIN dbo.WFDATASET 
	LEFT OUTER JOIN dbo.WFDATASET_VALUE ON dbo.WFDATASET.ID_DATASET = dbo.WFDATASET_VALUE.ID_DATASET ON dbo.WFPROCESS_INST_RELDATA.ID_DATASET = dbo.WFDATASET.ID_DATASET 
	INNER JOIN dbo.USERS ON dbo.WFPROCESS_INST.ID_USER_REQUESTER = dbo.USERS.ID_USER 
	INNER JOIN dbo.DIRECTORY ON dbo.USERS.ID_DIRECTORY = dbo.DIRECTORY.ID_DIRECTORY 
	INNER JOIN dbo.WFPROCESS_RELDATA ON dbo.WFPROCESS.ID_PROCESS = dbo.WFPROCESS_RELDATA.ID_PROCESS AND dbo.WFRELDATA.ID_RELDATA = dbo.WFPROCESS_RELDATA.ID_RELDATA 
	WHERE
		 (dbo.WFDATASET.ID_DATATYPE <> N''FILE'') AND (dbo.WFPROCESS_INST.TEST <> N''Y'')
		 AND (dbo.WFPROCESS_INST.DESCRIPTION = N''Special Freight Authorisation Form'')
	SELECT * FROM #Test ORDER BY RequestID, Data_Value
	DROP TABLE #Test

在此先感谢

Thanks in advance

推荐答案

检查一下，
http://www.kodyaz.com/article/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx [

Check this out,
http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx[^]

Try pivot query yourself, and come back if you get stuck somewhere.

请尝试在SQL Server中使用PIVOT运算符. 单击此处了解更多详细信息

Please try to use PIVOT operator in SQL server. click here for more details

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