Alternative way with which I can execute the stored procedure which generates dynamic columns, which can be supplied to PIVOT in the "in clause"

Question

I am working on a query in sql server management studio 2012 which generates dynamic columns, as we have used pivot to convert rows to columns. Due to some constraints we cannot use stored procedure to return the pivoted result set. In order to achieve the same result set with dynamic columns using pivot, I have tried the following ways, but stuck in some or the other issue.

Using view, here we cannot declare the variables, hence we cannot have a variable which holds the dynamic columns and pass it into PIVOT.
Using table-valued function.

a) One approach where we can declare variables in function and supply the dynamic columns in pivot, requires static columns to be specified as output columns. But my query generates dynamic columns.

b) Another approach where we cannot declare variables and need not mention the output columns, is shown in the sample posted. Here I am able to return the pivoted result set, but as mentioned since we cannot declare variables we cannot supply the dynamic columns in pivot.

ALTER FUNCTION [dbo].[func_A] 
    ()
RETURNS TABLE
AS
RETURN  
(
    WITH cte AS
    (
       SELECT *
       FROM 
           (SELECT
                DD.DesignId, DD.FactoryId, DD.DesignDescription, 
                DD.OfferingTree, DD.LeadTime, DD.AvailableStatus, 
                TS.ParameterValueFrom, TS.ParameterName 
            FROM
                dbo.TechnicalSpecification AS TS 
            INNER JOIN 
                DesignData AS DD ON TS.DesignId = DD.DesignId) ps
PIVOT
(
    MAX(ParameterValueFrom) 
    FOR ParameterName IN ( [**Bushing Family**])) AS pvt   
)

SELECT *
FROM cte
)

The bold variable is where we need to supply the dynamic columns.

c) Another approach using OpenRowSet as shown below

    SELECT * 
    INTO #MyTempTable 
    FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;', 'EXEC getBusinessLineHistory')

This approach says we need to install SQLNCLI. We are in the process of installing SQLNCLI. Will this approach work?

Is there any alternative way with which I can execute the stored procedure which generates dynamic columns, which can be supplied to PIVOT in the "in clause"?

score 0 · Answer 1 · answered May 24 '17 at 12:14

0

I am able to execute the stored procedure using SQLNCLI

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=(machinename);Trusted_Connection=yes;', 'EXEC procedurename'

answered May 24 '17 at 12:14

Jennifer Jes

35
5

Alternative way with which I can execute the stored procedure which generates dynamic columns, which can be supplied to PIVOT in the "in clause"

1 Answers1