August 9, 2016 at 5:18 pm
Good day,
I am struggling to solve this problem. While I am able to do a relatively straight forward pivot, I am unable to solve the following situation. Basically I have two steps I need to complete with the following data.
First, i want pivot on the color values (they are predefined, not unlimited/dynamic so to speak). I would like to put a binary indicator in the column, ie: an X.
Now here is the tricky part (for me) and completely outside of my skill set, I want to pivot on the style values, except I want to use a a predefined set of names for the pivot columns (the style column values do not become the pivot column name). For example, these are the column names I would use: [Style1], [Style2], [Style3], [Style4], [Style5]. These are fixed in number, five in this example. Where there is more than one style attributed to a car brand/model, it would simply fall into the next column ie [style2]. Again, limited to five. I hope this makes some sense.
sample data:
CREATE TABLE ##MyTable(
Brandvarchar(20),
Modelvarchar(20),
Colorvarchar(20),
Stylevarchar(20));
INSERT INTO ##MyTable
VALUES( 'Honda', 'Accord', 'Grey', 'Coupe'),
( 'Honda', 'Accord', 'Black', 'Sedan'),
( 'Honda', 'Accord', 'White', 'Wagon'),
( 'Honda', 'Accord', 'White', 'Unknown'),
( 'Honda', 'Accord', 'Red', 'Wagon'),
( 'Toyota', 'Camry', 'Red', 'Sedan'),
( 'Toyota', 'Camry', 'Grey', 'Sedan');
The output would be: (Sorry if the output example looks bad. I was not sure how to show a clean output using text. I attached an image, which I did in excel, that shows the proper output.)
Brand | Model | Grey | Black | White | Red | Style1 | Style2 | Style3 | Style4 | Style5
Honda Accord X X X Coupe Sedan Wagon Unknown
Toyota Camry X X Sedan
Thank you in advance for any help. It is greatly appreciated!
August 9, 2016 at 8:18 pm
Looks like you've got the pivot working to get to the colors into columns. To get to the Style try introducing a ROW_NUMBER into the mix and using that to place the values into the proper columns.
select
ROW_NUMBER()
over (partition by Brand, Model order by Style) as brand_model_style_ordinal,
*
from ##MyTable
order by Brand, Model, Style;
Note that the CROSS TAB query form may be easier to write than using PIVOT for this but YMMV.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 9, 2016 at 10:35 pm
Orlando Colamatteo (8/9/2016)
Looks like you've got the pivot working to get to the colors into columns. To get to the Style try introducing a ROW_NUMBER into the mix and using that to place the values into the proper columns.
Thank you for the reply, Orlando. Unfortunately I do not have the pivot working to get color into the columns. I can do that part, (minus knowing how to place an 'X' as a value rather than a numerical value) if I did not have to then get the five style columns outputted along with it. That is where it is outside of my skill level.
August 10, 2016 at 12:47 am
Try this out...ended up using DENSE_RANK instead of ROW_NUMBER in order to address dupe Styles:
with cte as (
select DENSE_RANK() over (partition by Brand, Model order by Style) as brand_model_style_ordinal,
*
from ##MyTable
)
select Brand,
Model,
max(case when Color = 'Grey' then 'X' else '' end) as Grey,
max(case when Color = 'Black' then 'X' else '' end) as Black,
max(case when Color = 'White' then 'X' else '' end) as White,
max(case when Color = 'Red' then 'X' else '' end) as Red,
max(case when brand_model_style_ordinal = 1 then Style else '' end) as Style1,
max(case when brand_model_style_ordinal = 2 then Style else '' end) as Style2,
max(case when brand_model_style_ordinal = 3 then Style else '' end) as Style3,
max(case when brand_model_style_ordinal = 4 then Style else '' end) as Style4,
max(case when brand_model_style_ordinal = 5 then Style else '' end) as Style5
from cte
group by Brand, Model
order by Brand, Model;
This result still seems a bit odd but mostly follows your expected results. I hesitate to think this is correct namely because, for example, in the original set there is a row for a Grey Honda Accord Coupe but not a Grey Honda Accord Sedan yet in your expected results and in the results of the above query it would appear to the reader that a Grey Honda Accord Sedan were an option.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 10, 2016 at 8:11 am
For more information and a better understanding of the method that Orlando is using, please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/63681/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply