pivot tables in stored procedure

See more:

SQL-Server-2005

SQL

SQL-Server

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