February 21, 2017 at 3:54 pm
I have a record set of article numbers with corresponding specification parameters.
ArticleNumber | SpecName | SpecValue
AB12345 | Type | beverage
AB12345 | Unit | 6 pack
AB12345 | Content | beer
CD67891 | Type | beverage
CD67891 | Unit | bottle
CD67891 | Content | wine
CD67891 | Size| 1L
I'd like to write a query that returns 1 record per distinct article number, each SpecName as a Column with the corresponding SpecValue:
ArticleNumber | Type | Unit | Content | Size
AB12345 | beverage | 6 pack | beer | NULL
CD67891 | beverage | bottle | wine | 1L
Any advice would be appreciated.
February 21, 2017 at 5:31 pm
Please supply DDL and sample data in a digestible format for T-SQL. You'll get a much quicker answer.
One solution, using PIVOT:CREATE TABLE #Sample (ID VARCHAR(10),
[Name] VARCHAR(10),
[Value] VARCHAR(10));
GO
INSERT INTO #Sample
VALUES ('AB12345','Type','beverage'),
('AB12345','Unit','6 pack'),
('AB12345','Content','beer'),
('CD67891','Type','beverage'),
('CD67891','Unit','bottle'),
('CD67891','Content','wine'),
('CD67891','Size','1L');
GO
SELECT *
FROM #Sample
SELECT *
FROM (SELECT ID, [Name], [Value]
FROM #Sample) AS Src
PIVOT ( MAX([Value])
FOR [Name] IN ([Type], [Unit], [Content], [Size])
) AS Pvt;
GO
DROP TABLE #Sample
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 22, 2017 at 2:53 am
Here is another solution using CROSS-TABS.
Thanks for the sample data Thom.
CREATE TABLE #Sample (ID VARCHAR(10),
[Name] VARCHAR(10),
[Value] VARCHAR(10));
GO
INSERT INTO #Sample
VALUES ('AB12345','Type','beverage'),
('AB12345','Unit','6 pack'),
('AB12345','Content','beer'),
('CD67891','Type','beverage'),
('CD67891','Unit','bottle'),
('CD67891','Content','wine'),
('CD67891','Size','1L');
GO
SELECT *
FROM #Sample
SELECT ID,
MAX( CASE WHEN [Name] = 'Type' THEN [Value] ELSE NULL END ) AS [Type],
MAX( CASE WHEN [Name] = 'Unit' THEN [Value] ELSE NULL END ) AS [Unit],
MAX( CASE WHEN [Name] = 'Content' THEN [Value] ELSE NULL END ) AS [Content],
MAX( CASE WHEN [Name] = 'Size' THEN [Value] ELSE NULL END ) AS [Size]
FROM #Sample
GROUP BY ID;
GO
DROP TABLE #Sample
Both the methods have been explained really well by Jeff Moden in the articles mentioned below for reference
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 22, 2017 at 6:59 am
Thank you. Would there also be a way in which I don't have to specify each Column by name?
February 22, 2017 at 7:30 am
OJDev - Wednesday, February 22, 2017 6:59 AMThank you. Would there also be a way in which I don't have to specify each Column by name?
Yes. You'd need to make a dynamic Pivot or Crosstab (I find the Crosstab to be easier to make dynamic but that's just my opinion). Here's an article that explains how to do it.
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2017 at 7:38 am
Jeff Moden - Wednesday, February 22, 2017 7:30 AMOJDev - Wednesday, February 22, 2017 6:59 AMThank you. Would there also be a way in which I don't have to specify each Column by name?Yes. You'd need to make a dynamic Pivot or Crosstab (I find the Crosstab to be easier to make dynamic but that's just my opinion). Here's an article that explains how to do it.
http://www.sqlservercentral.com/articles/Crosstab/65048/
It may appear to just be semantics, but there may be a good reason to choose (or NOT to choose) one method over the other, as it may well depend on how this data will be consumed. Thus, if this is going to feed a reporting tool, such as Crystal Reports or SSRS, or into an SSIS package, not specifying column names might well be a particularly BAD idea, whereas when feeding this into an inner part of a larger query, it may not matter quite as much. I always try to keep the ultimate use of the data in mind, along with any performance considerations, before choosing a particular method. In my world, performance tends to win most of those battles.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 22, 2017 at 7:44 am
OJDev - Wednesday, February 22, 2017 6:59 AMThank you. Would there also be a way in which I don't have to specify each Column by name?
Wondering if you should be looking at geting your presentation layer to do this. How is the end result going to be consumed? SSRS, Excel, other? If you need to transpose your data, and the columns names and number of could vary, many of these tools have much better ways of doing it than SQL. For example, SSRS Matrices, and Excel Pivot tables; both do the job very well, and, in my opinion, are much easier to use for transposing data.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 22, 2017 at 8:43 am
sgmunson - Wednesday, February 22, 2017 7:38 AMJeff Moden - Wednesday, February 22, 2017 7:30 AMOJDev - Wednesday, February 22, 2017 6:59 AMThank you. Would there also be a way in which I don't have to specify each Column by name?Yes. You'd need to make a dynamic Pivot or Crosstab (I find the Crosstab to be easier to make dynamic but that's just my opinion). Here's an article that explains how to do it.
http://www.sqlservercentral.com/articles/Crosstab/65048/It may appear to just be semantics, but there may be a good reason to choose (or NOT to choose) one method over the other, as it may well depend on how this data will be consumed. Thus, if this is going to feed a reporting tool, such as Crystal Reports or SSRS, or into an SSIS package, not specifying column names might well be a particularly BAD idea, whereas when feeding this into an inner part of a larger query, it may not matter quite as much. I always try to keep the ultimate use of the data in mind, along with any performance considerations, before choosing a particular method. In my world, performance tends to win most of those battles.
I agree whole heartedly. Except for sliding date windows, dynamic column naming is fraught with problems especially because it smacks of the ol' "One True Lookup Table" scenario. Hopefully, the OP will respond as to what the ultimate goal of all this is.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply