June 2, 2016 at 6:40 am
Apologize in advance if this has been answered already as I couldn't find what I'm looking for.
I would like to have a table of part numbers with attributes such that I can join a part master table to this attribute table to get a list of valid attributes for each part in the part master. The attributes will vary for different part numbers depending on product line. For example, part number ABC might have attribute names OD, ID and Width while part number XYZ might only have Color and Length. The records would look something like this:
ABC, OD, 10.125
ABC, ID, 8.125
ABC, WIDTH, 6.500
XYZ, COLOR, Blue
XYZ, LENGTH, 16.375
No problem there. What I want to do next is pivot the records so that I can create specification sheets for like products (by product line). The tables by product line would have the attribute name for the field name. For example, part ABC is in the product group "Round Widgets" and part XYZ is under product group "Long Widgets". The query table for round widgets would have fields PN, OD, ID, and WIDTH while the query table for Long Widgets would have fields PN, COLOR and LENGTH. Each of the field names would be the attribute name from the table described above and the part number and attribute values would be the record data.
For pivoted table fields PN, OD, ID, and WIDTH the data would look like this:
ABC, 10.125, 8.125, 6.500
I've seen many examples of using PIVOT but most of them hard code the columns needed. The query that would feed the pivot would already be filtered by product group so that each part number in the query should have the same number of fields to pivot.
FYI, this isn't my "day job" so to speak (mechanical engineer by trade) so while I'm comfortable with basic SQL I'm by nowhere near an expert 🙂
Thanks in advance for any help...
June 2, 2016 at 7:39 am
Dynamic pivoting is not the easiest thing, but once you understand how it works, it's really simple.
The first part is to understand how to do it in a static way. I'll try to explain it quickly, but you should go to the articles referenced at the end of this post.
First we need to set your sample data in a consumable way.
CREATE TABLE #Sample(
PN char(3),
Attribute varchar(10),
Value varchar(20)
);
INSERT INTO #Sample
VALUES
( 'ABC', 'OD' , '10.125'),
( 'ABC', 'ID' , '8.125 '),
( 'ABC', 'WIDTH' , '6.500 '),
( 'XYZ', 'COLOR' , 'Blue '),
( 'XYZ', 'LENGTH', '16.375');
You need to realize that you can't return these in the same result set, unless you return 5 columns for the attributes.
Then we do the static way of the pivot using cross tabs (it's my preferred option and has some advantages over PIVOT)
SELECT PN
,MAX( CASE WHEN Attribute = 'OD' THEN Value ELSE '' END) AS [OD]
,MAX( CASE WHEN Attribute = 'ID' THEN Value ELSE '' END) AS [ID]
,MAX( CASE WHEN Attribute = 'WIDTH' THEN Value ELSE '' END) AS [WIDTH]
FROM #Sample
WHERE PN = 'ABC'
GROUP BY PN;
SELECT PN
,MAX( CASE WHEN Attribute = 'COLOR' THEN Value ELSE '' END) AS
,MAX( CASE WHEN Attribute = 'LENGTH' THEN Value ELSE '' END) AS [LENGTH]
FROM #Sample
WHERE PN = 'XYZ'
GROUP BY PN;
We can divide the code in three parts, two static and one dynamic. The first part is static and contains the SELECT and the grouping column(s), in this case PN. The second part is dynamic and varies according to the part and its attributes, we'll work on creating that. The last part is static as well and has the FROM clause and all that comes after it.
The first and the third part can be converted easily to strings, the second one needs some work and we'll do it with the string concatenation method posted in the link below. It would look like this:
SELECT ( SELECT CHAR(9) + ',MAX( CASE WHEN Attribute = ''' + Attribute + ''' THEN Value ELSE '''' END) AS [' + Attribute + ']' + CHAR(10)
FROM #Sample
WHERE PN = @PN
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')
If we join all the parts, it would look like this:
DECLARE @PN char(3),
@SQL nvarchar(MAX);
SET @PN = 'ABC'
SELECT @SQL = N'SELECT PN ' + NCHAR(10) +
( SELECT CHAR(9) + ',MAX( CASE WHEN Attribute = ''' + Attribute + ''' THEN Value ELSE '''' END) AS [' + Attribute + ']' + CHAR(10)
FROM #Sample
WHERE PN = @PN
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)') +
N'FROM #Sample ' + NCHAR(10) +
N'WHERE PN = @PN ' + NCHAR(10) +
N'GROUP BY PN;';
PRINT @SQL;
As you can see, we have the code created, we just need to execute it properly by using sp_executesql.
EXEC sp_executesql @SQL, N'@PN char(3)', @PN;
I hope I made some sense. Read the articles below and ask any questions you might have.
Cross tabs and Pivots
Part 1: http://www.sqlservercentral.com/articles/T-SQL/63681/
Part 2: http://www.sqlservercentral.com/articles/Crosstab/65048/
String concatenation: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
June 2, 2016 at 12:50 pm
Thanks Luis. That's a lot for me to digest, and while I get the gist of it I think it may be overkill for what I'm trying to accomplish.
We have a limited number of product lines, and since I need create product-line-specific specification sheets anyway it may be easier for me to simply create a crosstab for each one. Now that I'm thinking a bit more about this, the benefit of individual queries by product line might be beneficial in other ways as well.
I'll ponder some more, but sincerely appreciate the help!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply