question

Sebeestiaan avatar image
Sebeestiaan asked

Problem with converting SQl PIVOT query

I'm new to stored procedures and trying to convert an access pivot query to an stored procedure. I have found some different articles about using stored procedures to make dynamic pivot tables. I understand the principle of the script, but I get stock in the second part of the script. starts at: "-- Building the query appending columns" Can someone please help me to solve this question? **Original Access query** TRANSFORM First(Properties_STR.Property_STR_Value) AS EersteVanProperty_STR_Value SELECT Projects.Project_Name, Projects.Project_State, Projects.Project_Path FROM Projects INNER JOIN Properties_STR ON Projects.[Project_ID] = Properties_STR.[Project_ID] GROUP BY Projects.Project_Name, Projects.Project_State, Projects.Project_Path PIVOT Properties_STR.Property_ID **SQL Stored Procedure** -- Getting distinct Dates into a temporary table #Dates SELECT DISTINCT convert(varchar, Property_ID, 106) AS [IDs] INTO #PropIDs FROM dbo.Properties_STR ORDER BY [IDs] -- Building a comma separated list of Dates in #Dates DECLARE @cols varchar(1000) SELECT @cols = COALESCE ( @cols + ",[" + [IDs] +"]", "[" + [IDs] + "]" ) FROM #PropIDs -- Building the query appending columns DECLARE @qry varchar(4000) SET @qry = 'SELECT Project_Name, Project_State, Project_Path, ' + @cols + ' FROM ( SELECT Projects.Project_Name, Projects.Project_State, Projects.Project_Path, convert(varchar, Property_ID, 106) AS [IDs] , Property_STR_Value FROM dbo.Properties_STR ) p PIVOT ( First(Property_STR_Value) FOR [IDs] IN (' + @cols + ') ) AS pvt' -- Executing the query EXEC(@qry) -- Dropping temporary table DROP TABLE #PropIDs
pivotinner joinstored procedure
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What is FIRST()? You need to have a legit aggregate function.
0 Likes 0 ·
a quick google shows that the First function Is only available in Access ;-( I need to return a string value.
0 Likes 0 ·
Can you please edit your question with an example of the initial data and the desired output? That would help a lot!
0 Likes 0 ·
Also try to use MAX() instead of FIRST().
0 Likes 0 ·
Or try using TOP 1 with an ORDER BY
0 Likes 0 ·
Sebeestiaan avatar image
Sebeestiaan answered
Problem solved, thank you all. **Final solution** SELECT DISTINCT convert(varchar, Property_ID, 106) AS [IDs] INTO #PropIDs FROM dbo.Properties_STR ORDER BY [IDs] -- Building a comma separated list of Dates in #Dates DECLARE @cols varchar(1000) SELECT @cols = COALESCE ( @cols + ',[' + [IDs] +']', '[' + [IDs] + ']' ) FROM #PropIDs -- Building the query appending columns DECLARE @qry varchar(4000) SET @qry = 'SELECT '+@cols +' pvt.Project_ID, dbo.Projects.[Project_Name], dbo.Projects.[Project_Path] FROM ( SELECT [Property_ID], Property_STR_Value, Project_ID FROM dbo.Properties_STR ) p PIVOT ( MAX(Property_STR_Value) FOR [Property_ID] IN ('+@cols +'[0]) ) as pvt INNER JOIN dbo.Projects ON dbo.Projects.Project_ID = pvt.Project_ID ORDER BY pvt.Project_ID;' -- Executing the query EXEC(@qry) -- Dropping temporary table DROP TABLE #PropIDs
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Good job! There is a solution to the last comma problem if you use STUFF with FOR XML PATH. Just google -- dynamic pivot table sql server -- you will see the examples.
0 Likes 0 ·
Thanks DenisT
0 Likes 0 ·
Sebeestiaan avatar image
Sebeestiaan answered
I have two tables "project" and "Properties_STR". Project is a list of all projects with ID, name and status. The second table contains all project data, the different projectdata is not stored in columns but in rows. The combination of project_id and Property_ID returns the Property_STR_Value value. I want to transform the Property_ID values into column names. Maybe the screenshot explains it beter. ![alt text][1] [1]: /storage/temp/2397-eplanp8pivot.png

eplanp8pivot.png (104.1 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Did you try MAX()?
0 Likes 0 ·
Sebeestiaan avatar image
Sebeestiaan answered
Part 1 is almost working. :-D In the first part of the stored procedure, the column list generates an unwanted comma at the end of the string. Fixed it with a manual added column [0] in the second part of the stored procedure. (Don't no how to remove it within the script) During testing and debugging the query, it was totally messed up by making changes. So I need to rebuild it by scratch. @DenisT the MAX function is working great Only the Project_ID is not shown in the results. It shows the rownumber not the value. -- Getting distinct property_ids into a temporary table #PropIDs SELECT DISTINCT convert(varchar, Property_ID, 106) AS [IDs] INTO #PropIDs FROM dbo.Properties_STR ORDER BY [IDs] -- Building a comma separated list of Dates in #Dates DECLARE @cols varchar(1000) SELECT @cols = COALESCE ( @cols + ',[' + [IDs] +']', '[' + [IDs] + ']' ) FROM #PropIDs -- Building the query appending columns DECLARE @qry varchar(4000) SET @qry = 'SELECT [Project_ID] '+@cols +' [0] FROM ( SELECT [Project_ID], [Property_ID], Property_STR_Value FROM dbo.Properties_STR ) p PIVOT ( MAX(Property_STR_Value) FOR [Property_ID] IN ('+@cols +'[0]) ) AS pvt ORDER BY pvt.Project_ID;' -- Executing the query EXEC(@qry) -- Dropping temporary table DROP TABLE #PropIDs Part 2 Next challenge I need to join this data with an extra table named Project where I need to return the columns Project_Path and Project _Name, where the Project_ID is the relation between the 2 tables. Something like SELECT dbo.Projects.Project_Name, dbo.Projects.Project_Path + '@colls' + FROM dbo.Projects INNER JOIN dbo.Properties_STR ON dbo.Projects.Project_ID = pvt.Project_ID Please advise?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

And why is it a challenge?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.