Defining dynamic PIVOT sql query in SQL Server

Question

Currently I have a static pivot sql query defined in a stored procedure in sql server:

ALTER PROCEDURE [dbo].[MonthRepo] 
    -- Add the parameters for the stored procedure here
    @from datetime,
    @to datetime
AS
BEGIN
    DECLARE @cols nvarchar(12)
    DECLARE @query nvarchar(max)

    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT *
    FROM (
        SELECT ROUND(ds.ct_quot_rate,0) AS Quote,
               ROUND(ds.ct_quot_rate,0) AS Quote_Out,
               ds.isin
        FROM ds
        WHERE ds.datum >= @from AND ds.datum <= @to
    ) tbl
    PIVOT (
        COUNT(Quote)
        FOR isin IN(AB000001,
                    AB000002, 
                    AB000003,
                    AB000004,
                    AB000005)
    ) piv
END

How can I define this static code in dynamic query? I have declared 2 variables.

possible dublicate of: stackoverflow.com/questions/30295640/… — user4622594
– user4622594, Commented Jul 21, 2015 at 9:50
@CeOnSql I'm getting this error: Conversion failed when converting date and/or time from character string. — yuro
– yuro, Commented Jul 21, 2015 at 13:07

Horia · Accepted Answer · 2015-07-21 13:35:28Z

4

I think you're after something like this:

    ALTER PROCEDURE [dbo].[MonthRepo] 
        -- Add the parameters for the stored procedure here
        @from datetime,
        @to datetime
    AS
    BEGIN

        DECLARE @cols nvarchar(max)
        DECLARE @query nvarchar(max)

        SET NOCOUNT ON;

        WITH vals AS (
            SELECT DISTINCT isin
            FROM   ds
        )
        SELECT  @cols = COALESCE(@cols + ',','') + '[' + isin + ']'
        FROM    vals

        SET @query = '
            SELECT *
            FROM (
                SELECT ROUND(ds.ct_quot_rate,0) AS Quote,
                       ROUND(ds.ct_quot_rate,0) AS Quote_Out,
                       ds.isin
                FROM ds
                WHERE ds.datum >= @from_param AND ds.datum <= @to_param
            ) tbl
            PIVOT (
                COUNT(Quote)
                FOR isin IN(' + @cols + ' )
            ) piv'

        EXECUTE sp_executesql @query, N'@from_param DATETIME, @to_param DATETIME', @from_param = @from, @to_param = @to
    END

edited Jul 21, 2015 at 13:35

answered Jul 21, 2015 at 9:51

Horia

1,61211 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

yuro Over a year ago

your definition of @cols is static. I need a definition where the isins automatically added to the stored procedure when I add a new isin in the table ds.

Horia Over a year ago

I`ve just added the dynamic pick up of values as they are added to ds table - please have a look above again.

yuro Over a year ago

I'm getting an error Unclosed quotation mark after the character string 'DE0)) piv

Horia Over a year ago

probably because @cols length was 100 - changed it to max and it generates a valid query - please have a look.

mohan111 · Accepted Answer · 2015-07-21 14:16:39Z

0

CREATE PROCEDURE [dbo].[MonthRepo] 
        -- Add the parameters for the stored procedure here
        @from VARCHAR(20),
        @to VARCHAR(20)
    AS
    BEGIN
    IF OBJECT_ID('tempdb..#T') IS NOT NULL 
DROP TABLE #T

create  table #T
(
  datnum varchar(20),
  quote INT,
  isin VARCHAR(20)
)
insert into #T (datnum,quote,isin)values ('2015-01-01',100,'AB000001'),
('2015-01-01',100,'AB000002'),
('2015-01-02',98,'AB000003'),
('2015-01-02',70,'AB000001'),
('2015-01-03',100,'AB000001')
    DECLARE @statement NVARCHAR(max)
    ,@columns NVARCHAR(max)



SELECT @columns = Isnull(@columns + ', ', '') + N'[' + tbl.isin+ ']'
FROM   (SELECT DISTINCT isin
        FROM   #T) AS tbl
--SELECT @columns
SELECT @statement = '  SELECT *
    FROM (
        SELECT ROUND(ds.quote,0) AS Quote,
               ROUND(ds.quote,0) AS Quote_Out,
               ds.isin
        FROM #T ds
        WHERE ds.datnum >=  CONVERT(datetime,'+''''+@from+''' ,105) AND ds.datnum <=  CONVERT(datetime,'''+@to+''' ,105) 

    ) tbl
    PIVOT (
        COUNT(Quote)
        FOR isin IN(' + @columns+ ')) as PVT

' 

PRINT @statement
EXEC sp_executesql @statement = @statement


    END


EXEC [MonthRepo] @from = '2015-01-01',@to = '2015-01-10'

edited Jul 21, 2015 at 14:16

answered Jul 21, 2015 at 9:54

mohan111

8,8854 gold badges35 silver badges61 bronze badges

2 Comments

yuro Over a year ago

I get this error msg: Conversion failed when converting date and/or time from character string.

mohan111 Over a year ago

@yuro sorry for the late now only i have seen the comment

Collectives™ on Stack Overflow

Defining dynamic PIVOT sql query in SQL Server

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related