Link to home
Start Free TrialLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

SQL Server Pivot or "dynamic" query data available for join

I have the following code that creates the data I want
CREATE TABLE #CourseSales
(Course VARCHAR(50),Year INT,Earning MONEY)
GO
--Populate Sample records
INSERT INTO #CourseSales VALUES('.NET',2012,10000)
INSERT INTO #CourseSales VALUES('Java',2012,20000)
INSERT INTO #CourseSales VALUES('.NET',2012,5000)
INSERT INTO #CourseSales VALUES('.NET',2013,48000)
INSERT INTO #CourseSales VALUES('Java',2013,30000)
GO



DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
 
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses
 
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
  N'SELECT Year, ' + @ColumnName + '
    FROM #CourseSales
    PIVOT(SUM(Earning)
          FOR Course IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

What I need is for the result to be available to join on an actual table Individuals

So I can select like this...

Select i.FirstName, i.LastName, i.BirthDate, r.*
From Individuals i
JOIN @resultdata r on i.IndividualID, r.IndividualID
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

there is no field named as "IndividualID" in your "resultdata", are you missing something there?
Avatar of Larry Brister

ASKER

Hey Ryan...
Sorry...
My query above that works... is just me making a pivot that would work

For the purposes of this question lets join on "year" to a second  actual table

So... the basic premis is that I have available at runtime a join between my dynamically created table and a standard table
Here is the actual query with "modified" data
I need to join that on the Individuals Table

IF OBJECT_ID('tempdb..#Demo') IS NOT NULL DROP TABLE #Demo

CREATE TABLE #Demo
 (IndividualID BIGINT,QuestionText VARCHAR(50),AnswerText VARCHAR(200))
 GO
 --Populate Sample records
 INSERT INTO #Demo 
 SELECT TOP 10 INdividualID, QuestionText, AnswerText FROM dbo.vwPromotionSurveyIndividualAnswers ORDER BY IndividualID DESC
 GO

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) ,
        @PivotColumnNames AS NVARCHAR(MAX) ,
        @PivotSelectColumnNames AS NVARCHAR(MAX);
--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames = ISNULL(@PivotColumnNames + ',', '') + QUOTENAME(QuestionText)
FROM   (   SELECT DISTINCT QuestionText
           FROM   #Demo ) AS QuestionTexts;
--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames = ISNULL(@PivotSelectColumnNames + ',', '') + 'ISNULL(' + QUOTENAME(QuestionText) + ', '''') AS ' + QUOTENAME(QuestionText)
FROM   (   SELECT DISTINCT QuestionText
           FROM   #Demo ) AS QuestionTexts;
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery = 'SELECT IndividualID, ' + @PivotSelectColumnNames + '
FROM #Demo
PIVOT(MAX(AnswerText)
FOR QuestionText IN ('         + @PivotColumnNames + ')) AS PVTTable';
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OK... using my sql above... how
Never mind... I have it...

IF OBJECT_ID('tempdb..#Demo') IS NOT NULL DROP TABLE #Demo

CREATE TABLE #Demo
 (IndividualID BIGINT,QuestionText VARCHAR(50),AnswerText VARCHAR(200))
 GO
 --Populate Sample records
 INSERT INTO #Demo
 SELECT TOP 10 INdividualID, QuestionText, AnswerText FROM dbo.vwPromotionSurveyIndividualAnswers ORDER BY IndividualID DESC
 GO

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) ,
        @PivotColumnNames AS NVARCHAR(MAX) ,
        @PivotSelectColumnNames AS NVARCHAR(MAX);
--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames = ISNULL(@PivotColumnNames + ',', '') + QUOTENAME(QuestionText)
FROM   (   SELECT DISTINCT QuestionText
           FROM   #Demo ) AS QuestionTexts;
--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames = ISNULL(@PivotSelectColumnNames + ',', '') + 'ISNULL(' + QUOTENAME(QuestionText) + ', '''') AS ' + QUOTENAME(QuestionText)
FROM   (   SELECT DISTINCT QuestionText
           FROM   #Demo ) AS QuestionTexts;
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery = 'SELECT IndividualID, ' + @PivotSelectColumnNames + '
INTO #temp FROM #Demo
PIVOT(MAX(AnswerText)
FOR QuestionText IN ('         + @PivotColumnNames + ')) AS PVTTable

Select  i.FirstName, i.LastName, t.* from Individuals i join  #temp t on i.IndividualID = t.IndividualID';
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery;
you can try like:

IF OBJECT_ID('tempdb..#Demo') IS NOT NULL DROP TABLE #Demo



CREATE TABLE #Demo
 (IndividualID BIGINT,QuestionText VARCHAR(50),AnswerText VARCHAR(200))
 GO
 --Populate Sample records
 INSERT INTO #Demo 
 SELECT TOP 10 INdividualID, QuestionText, AnswerText FROM dbo.vwPromotionSurveyIndividualAnswers ORDER BY IndividualID DESC
 GO

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) ,
        @PivotColumnNames AS NVARCHAR(MAX) ,
        @PivotSelectColumnNames AS NVARCHAR(MAX);
--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames = ISNULL(@PivotColumnNames + ',', '') + QUOTENAME(QuestionText)
FROM   (   SELECT DISTINCT QuestionText
           FROM   #Demo ) AS QuestionTexts;
--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames = ISNULL(@PivotSelectColumnNames + ',', '') + 'ISNULL(' + QUOTENAME(QuestionText) + ', '''') AS ' + QUOTENAME(QuestionText)
FROM   (   SELECT DISTINCT QuestionText
           FROM   #Demo ) AS QuestionTexts;
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery = 'SELECT IndividualID, ' + @PivotSelectColumnNames + '
into #resultdata
FROM #Demo
PIVOT(MAX(AnswerText)
FOR QuestionText IN ('         + @PivotColumnNames + ')) AS PVTTable;

Select i.FirstName, i.LastName, i.BirthDate, r.*
From Individuals i
JOIN #resultdata r on i.IndividualID = r.IndividualID

';


--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery;

Open in new window