Pivot 3 column stored procedure query record set in SQL Server
-
Let's say I have a store procedure returning Value, Desired_Row_Header, and Desired_Column_Header. EXEC [dbo].[scsp_My_Query] Can I pivot the results of the stored procedure directly or do I have to embed the pivot in the stored procedure? I am currently fetching the results off the database and manually creating pivot tables in code. I'd like to move it on the database, especially if I can do the pivot based on the column position without having to lookup the names of each column.
-
Let's say I have a store procedure returning Value, Desired_Row_Header, and Desired_Column_Header. EXEC [dbo].[scsp_My_Query] Can I pivot the results of the stored procedure directly or do I have to embed the pivot in the stored procedure? I am currently fetching the results off the database and manually creating pivot tables in code. I'd like to move it on the database, especially if I can do the pivot based on the column position without having to lookup the names of each column.
Have you tried CREATE TABLE #tmp(Value varchar(20), Desired_Row_Header varchar(20), Desired_Column_Header varchar(20)) INSERT INTO #tmp exec scsp_My_Query Now do your pivot on #tmp
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
-
Have you tried CREATE TABLE #tmp(Value varchar(20), Desired_Row_Header varchar(20), Desired_Column_Header varchar(20)) INSERT INTO #tmp exec scsp_My_Query Now do your pivot on #tmp
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
-
No, I hadn't tried doing that. It sounds like a good idea and I will try it out. I have gotten some pivots to work with arbitrary # of columns, but I've had to use dynamic sql.
T2102 wrote:
I have gotten some pivots to work with arbitrary # of columns, but I've had to use dynamic sql.
Yea if the columns before execution time are not known then you will need to resort to dynamic SQL.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.