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