CODEWITHSUNDEEP
sqlpivot-tabledynamic-pivot
Joanna
Joanna

Reputation: 1

How to create a fixed table from a dynamic pivot in SQL?

I am trying to create a new table from the results of the dynamic pivot below. The query itself works fine. 

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(COMMON_NAME)
FROM (SELECT p.COMMON_NAME FROM eBird AS p
GROUP BY p.COMMON_NAME) AS x;
SET @sql = N'
SELECT SAMP_EVENT_ID, ' + STUFF(@columns, 1, 2, '') + '
FROM
(SELECT SAMP_EVENT_ID, COMMON_NAME, SUM(IIF(OBS_COUNT IS NULL, 0, 1)) AS       
Pres
FROM eBird GROUP BY SAMP_EVENT_ID, COMMON_NAME) AS j
PIVOT
(SUM(Pres) FOR COMMON_NAME IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')  + ')) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;

I have tried the typical
SELECT * INTO newtable FROM (subquery) But that didn't work. I get the errors "Msg 156, Level 15, State 1, Line 416 Incorrect syntax near the keyword 'DECLARE'. Msg 102, Level 15, State 1, Line 432 Incorrect syntax near ')'."

I have also tried modifying this section as so: SELECT @columns += N', p.' + QUOTENAME(COMMON_NAME) INTO sp_pivot FROM(SELECT p.COMMON_NAME FROM eBird AS p GROUP BY p.COMMON_NAME) AS x;

But, I get this error:
Msg 194, Level 15, State 1, Line 419 A SELECT INTO statement cannot contain a SELECT statement that assigns values to a variable.

I have spent quite a bit of time trying to figure this out by looking at previous questions and would appreciate any help. Thank you.

Upvotes: 0

Views: 90

Answers (1)

dcieslak
dcieslak

Reputation: 2715

The below works on MS SQL 2014.

 DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(COMMON_NAME)
FROM (SELECT p.COMMON_NAME FROM eBird AS p
GROUP BY p.COMMON_NAME) AS x;
SET @sql = N'
SELECT SAMP_EVENT_ID, ' + STUFF(@columns, 1, 2, '') + ' into NewTable
FROM
(SELECT SAMP_EVENT_ID, COMMON_NAME, SUM(IIF(OBS_COUNT IS NULL, 0, 1)) AS       
Pres
FROM eBird GROUP BY SAMP_EVENT_ID, COMMON_NAME) AS j
PIVOT
(SUM(Pres) FOR COMMON_NAME IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')  + ')) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;

Version from 2008 R2 that works:

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(COMMON_NAME)
FROM (SELECT p.COMMON_NAME FROM eBird AS p
GROUP BY p.COMMON_NAME) AS x;
SET @sql = N'
SELECT SAMP_EVENT_ID, ' + STUFF(@columns, 1, 2, '') + '  into NewTable2 
FROM
(SELECT SAMP_EVENT_ID, COMMON_NAME, SUM(case when OBS_COUNT is null then  0 else 1 end) AS       
Pres
FROM eBird GROUP BY SAMP_EVENT_ID, COMMON_NAME) AS j
PIVOT
(SUM(Pres) FOR COMMON_NAME IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')  + ')) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;

Upvotes: 1

Related Questions