Good Morning,
I have 2 views: ICCUDays which contains one record per account with fields ACCOUNT and ICCUDays, ICCUEnctrSelectedRevCatsDirCost which contains multiple records per account with fields ACCOUNT, UBCATEGORY, and DirectCost.
My Goal: To create a stored procedure that outputs one record per ACCOUNT with ICCUDays and DirectCost by UBCATEGORY. This will be a crosstab or pivot and has to allow for the possibility of nulls in one or more direct cost ubcategory bucket. Finally, this crosstab or pivot needs to be sent to a new table EnctrUBCatPivot.
Questions: What is the correct PIVOT syntax for the above scenario? Given that I want to ouptut direct cost for however many UBCATEGORY entries, how do I write the TSQL to iterate over these and pivot by account and UBCATEGORY? Is all this accomplished in one sproc, or does it have to be separated into multiple sprocs to write the results out to a table?
Here's the code I've written so far:
ALTER PROCEDURE [dbo].[spICCUMain]
-- Add the parameters for the stored procedure here
AS
declare @columns varchar(8000)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @columns = COALESCE(@columns + ',[' + cast(UBCATEGORYmid as varchar) + ']','[' + cast(UBCATEGORYmid as varchar)+ ']')
FROM vwICCUEnctrSelectedRevCatsDirCost
GROUP BY UBCATEGORYmid
DECLARE @query VARCHAR(8000)
SET @query = '
SELECT *
FROM vwICCUEnctrSelectedRevCatsDirCost
PIVOT
(
MAX(DirectCost)
FOR [UBCATEGORYmid]
IN (' + @columns + ')
)
AS p'
EXECUTE(@query)
END
This works fine in that it outputs Account and all the Direct Costs for each UBCATEGORY. However, I need to inner join to vwICCUDAYS on ACCOUNT to add a column to the pivot for ICCUDays. Final pivot columns should be Account, ICCUDays, Direct Cost for each UBCATEGORYmid.
I'm not very familiar with the coalesce syntax and thus cannot discern how to modify it to add further columns, nor am I sure how/where to add the inner join syntax to add ICCUDays.
Can someone point me in the proper direction? Thanks, Sid