I have a problem with pivoting a table on MSSQL. The main problem is, when I want to fetch row values in an inner query (with IN clause) I get syntax errors but when I type the values statically, it works.

So the question is, how can I use IN clause inside PIVOT clause?

The error I get is Incorrect syntax near SELECT expecting ID,Quoted_ID or '.' when I try inner select query.

Full sql query is listed below. `

CREATE OR ALTER FUNCTION [SCHEMA].[get_timeseries_by_campaign_id_and_period] (
    @campaign_id INT,
    @start_date DATE NULL,
    @end_date DATE NULL)
WITH timeseries_data AS
              WHEN t.data_type = 'int' THEN ti.datetime
              WHEN t.data_type = 'real' THEN tr.datetime
              WHEN t.data_type = 'text' THEN tt.datetime
              ELSE 'No Data'
          END AS timeseries_date,
              WHEN t.data_type = 'int' THEN ti.value
              WHEN t.data_type = 'real' THEN tr.value
              WHEN t.data_type = 'text' THEN tt.value
              ELSE 'No Data'
          END AS timeseries_value
   FROM [DATABASE].[SCHEMA].[hub_campaign] c
   INNER JOIN [DATABASE].[SCHEMA].[measurement] m ON c.campaign_id = @campaign_id AND m.campaign_id = c.campaign_id
   INNER JOIN [DATABASE].[SCHEMA].[timeseries] t ON t.measurement_id = m.measurement_id
   INNER JOIN [SCHEMA].[timeseries_view] tv ON tv.campaign_bk = c.campaign_bk
   LEFT OUTER JOIN [DATABASE].[SCHEMA].[timeseries_data_int] AS ti ON t.timeseries_id = ti.timeseries_id 
        AND COALESCE(@start_date, GETDATE() - 1) <= ti.datetime AND ti.datetime < COALESCE(@end_date, GETDATE())
   LEFT OUTER JOIN [DATABASE].[SCHEMA].[timeseries_data_real] AS tr ON t.timeseries_id = tr.timeseries_id
        AND COALESCE(@start_date, GETDATE() - 1) <= tr.datetime AND tr.datetime < COALESCE(@end_date, GETDATE())
   LEFT OUTER JOIN [DATABASE].[SCHEMA].[timeseries_data_text] AS tt ON t.timeseries_id = tt.timeseries_id
        AND COALESCE(@start_date, GETDATE() - 1) <= tt.datetime AND tt.datetime < COALESCE(@end_date, GETDATE())
timeseries_ids AS (
    SELECT CONCAT('[', timeseries_id, ']') as timeseries_id FROM timeseries_data
    SELECT timeseries_date, timeseries_id,  timeseries_value, data_type
    FROM timeseries_data
) tmp PIVOT (
    -- AVG(timeseries_value) FOR tmp.timeseries_id IN (SELECT timeseries_id FROM timeseries_ids)
    AVG(timeseries_value) FOR tmp.timeseries_id IN ([1], [7], [8], [9], [10], [11], [12], [13], [16], [14], [15], [17], [22], [26], [23], [25], [27])
)as pvt;

SELECT * FROM [SCHEMA].[get_timeseries_by_campaign_id_and_period](1, '2021-05-29 18:00:00.00', '2021-05-30 08:00:00.00');
  • 1
    Please have a look at Minimal, complete and verifiable example
    – McNets
    Jul 14 '21 at 12:51
  • Thanks for your advice. I wanted to be as clear as possible.
    – ilhnctn
    Jul 14 '21 at 12:54
  • 4
    The IN inside the PIVOT clause is not your regular IN predicate. Each item included there must be specified explicitly. What you are looking to do appears to be dynamic pivoting, and, coupled with the sql-server tag, that's the term that you want to search this or that site for in order to get a general picture of how it's typically done in T-SQL.
    – Andriy M
    Jul 14 '21 at 13:23
  • I thought here would be better for this question. Thanks for your advice.
    – ilhnctn
    Jul 15 '21 at 8:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.