asked
T-SQL SP Change Rows to Columns
Hello All, I am a relative newbie to SQL coding, although I have written a couple of SP and Functions now. Today I just threw together the following T-SQL to report all parts used in production database. There are only a few thousand parts. USE [dCSMSTG] GO SELECT Parts.Part , Parts.CachedDescription , PartAttributesList.PartAttribute , PartAttributes.AttributeValue , PartCategories.PartCategory , PartTypes.PartType , PartStatus.PartStatus FROM Parts , PartAttributesList , PartAttributes , PartCategories , PartTypes , [dCSMSTG].[dbo].[PartStatus] WHERE ( Parts.PartId = PartAttributes.PartId AND ( Parts.PartTypeID = PartTypes.PartTypeID ) AND ( Parts.PartStatusID = PartStatus.PartStatusID ) AND ( parts.PartCategoryID = PartCategories.PartCategoryID ) AND ( Partattributes.PartAttributeListID = PartAttributesList.PartAttributeListID ) AND ( Parts.Deleted = 0 ) AND ( PartTypes.IsAssembly = 0 ) ) ORDER BY Parts.Part This SQL reports each attribute that a part has, on separate rows like follows: ![alt text][1] Each row for a 'Part' is duplicated apart from the 'PartAttribute' column values. I would like to output the results so that there is only a single row, each 'PartAttribute' value belonging to a column instead. For example: ![alt text][2] **Q. Can anyone assist me with how I might change the SQL to do this please?** I did see references to Pivot tables and a few other row to column methods, but they seemed to relate to very simple data sets and I couldn't figure out how to use them for the above situation... [1]: /storage/temp/1282-zspartslist.gif [2]: /storage/temp/1283-zspartslist2.gif
Don't look at your query as a whole, just think about how you can pivot the PartAttribute with only the Part column. If you can get that simple pivot working, you can add it back into your initial query and join to it: INNER JOIN () AS PartAttributes ON Parts.Part = PartAttributes.Part As a quick aside, you might start writing your joins with the JOIN syntax rather than using a comma separated list of tables and the WHERE clause. The old syntax is on its way out and we should all be using the modern syntax.
Hi Ken, Would the following be what you had in mind for the Pivot? I cannot test until tomorrow as the database is at work and I was just trying to get a head start on it for tomorrow with some ideas to look into tomorrow, before everyone starts coming back for the new year. PIVOT ( COUNT(PartAttribute) FOR PartAttribute IN ([Family], [Type], [Size]. [Material], [Grade], [Voltage], [Rating], {Peoplesoft Item ID]) } pvt What concerns me with that, and I only just thought to mention this (as significant an omission as it is), the names of these attributes can change according to what "category" a Part belongs too (parts.PartCategoryID). To me this would mean that the above code won't work where the names are missing...
Having the names change based on PartCategoryID does make it interesting. To use PIVOT, you would need to have columns for each and every value of PartAttribute, so each part would have empty columns that did not apply to it. Could you concatenate the various PartAttribute values into a single column as a delimited string? Part PartAttributes ------- --------------------------- ZSP00001 Family: Bolt, Type: HEX HEAD, Size: M10 x 16MM, etc...
Thanks Ken. I've sorted it out and also corrected the "old" syntax, now that you helped by pointing out the error of my ways. I would post the final solution but really don't know the best place to do this? Happy New Year! Cheers, Colin
For the final code, please refer to this question where I have posted the answer.
http://ask.sqlservercentral.com/questions/109226/is-my-sp-reporting-services-compatible.html
15 People are following this question.
