Larry Brister
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
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
there is no field named as "IndividualID" in your "resultdata", are you missing something there?
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
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
ASKER
Here is the actual query with "modified" data
I need to join that on the Individuals Table
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK... using my sql above... how
ASKER
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.vwPromotionSurveyIndiv idualAnswe rs 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(@PivotSelectColumnN ames + ',', '') + '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;
IF OBJECT_ID('tempdb..#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.vwPromotionSurveyIndiv
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(@PivotSelectColumnN
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;