coldchillin
asked on
PIVOT Question - Select row values as columns
Let's say I have an Item table, Feature table, and ItemFeature table
Item: ItemID, ItemName
1 Car
2 Train
Feature: FeatID
1 Fast
2 Slow
3 Diesel
4 Steam
ItemFeature:ItemID,FeatID
1 1
1 3
2 1
2 2
2 3
2 4
I want to be able to return something like this:
ItemID Item Name Fast Slow Diesel Steam
1 Car Yes Yes
2 Train Yes Yes Yes Yes
And
ItemID Item Name Feat1 Feat2 Feat3 Feat4
1 Car Fast Diesel
2 Train Fast Slow Diesel Steam
OR possibly...
ItemID Item Name Feat1 Feat2 Feat3 Feat4
1 Car Fast Diesel
2 Train Fast Slow Diesel Steam
I have neved used a pivot table, not sure if that's the correct solution I'm looking for...
Item: ItemID, ItemName
1 Car
2 Train
Feature: FeatID
1 Fast
2 Slow
3 Diesel
4 Steam
ItemFeature:ItemID,FeatID
1 1
1 3
2 1
2 2
2 3
2 4
I want to be able to return something like this:
ItemID Item Name Fast Slow Diesel Steam
1 Car Yes Yes
2 Train Yes Yes Yes Yes
And
ItemID Item Name Feat1 Feat2 Feat3 Feat4
1 Car Fast Diesel
2 Train Fast Slow Diesel Steam
OR possibly...
ItemID Item Name Feat1 Feat2 Feat3 Feat4
1 Car Fast Diesel
2 Train Fast Slow Diesel Steam
I have neved used a pivot table, not sure if that's the correct solution I'm looking for...
oops, like this
select ItemID,
ItemName,
[1],
[2],
[3],
[4]
from (
select a.ItemID,
a.ItemName,
c.FeatID,
c.FeatName
from ItemFeature a
inner join Item b on a.ItemID = b.ItemID
inner join Feature c on a.FeatID = c.FeatID
) o
pivot (max(FeatName) for FeatID in ([1], [2], [3], [4])) p
If the number of features is unknow, then you can do something like this:
declare @strSQL varchar(max)
declare @cols varchar(max)
set @cols = stuff((select distinct '], [' + cast(FeatID as varchar(10))
from Feature order by 1 for xml path('')), 1, 2, '') + ']'
set @strSQL = 'select ItemID, ItemName, ' + @cols +
' from (
select a.ItemID,
a.ItemName,
c.FeatID,
c.FeatName
from ItemFeature a
inner join Item b on a.ItemID = b.ItemID
inner join Feature c on a.FeatID = c.FeatID
) o
pivot (max(FeatName) for FeatID in (' + @cols + ')) p'
exec(@strSQL)
ASKER
Thank you!
What is the significance of the first fields before the brackets of the first select with the inner select?
When I change it to select *, [1],[2]...
from ( select b.*,c.FeatID,c.FeatName... )
I have [1],[2]...appear more than once in a single row
What is the significance of the first fields before the brackets of the first select with the inner select?
When I change it to select *, [1],[2]...
from ( select b.*,c.FeatID,c.FeatName...
I have [1],[2]...appear more than once in a single row
ASKER
How about this:
pivot (max(FeatName) for FeatID in ([1], [2], [3], [4])) p
Is there anyway to change what is returned to a "Yes" or "X"? I tried to use a case statement but it didn't like it...
pivot (max(FeatName) for FeatID in ([1], [2], [3], [4])) p
Is there anyway to change what is returned to a "Yes" or "X"? I tried to use a case statement but it didn't like it...
>>
When I change it to select *, [1],[2]...
from ( select b.*,c.FeatID,c.FeatName... )
I have [1],[2]...appear more than once in a single row<<
Yes because the * there is declaring the columns [1] and [2] already. so you will have them more than once.
For your second question, you can do:
When I change it to select *, [1],[2]...
from ( select b.*,c.FeatID,c.FeatName...
I have [1],[2]...appear more than once in a single row<<
Yes because the * there is declaring the columns [1] and [2] already. so you will have them more than once.
For your second question, you can do:
select ItemID,
ItemName,
case when [1] is not null 'yes' else '' end as [1],
case when [2] is not null 'yes' else '' end as [2],
case when [3] is not null 'yes' else '' end as [3],
case when [4] is not null 'yes' else '' end as [4]
from (
select a.ItemID,
a.ItemName,
c.FeatID,
c.FeatName
ItemFeature a
inner join Item b on a.ItemID = b.ItemID
inner join Feature c on a.FeatID = c.FeatID
) o
pivot (max(FeatName) for FeatID in ([1], [2], [3], [4])) p
oops missed some "then" there
...
case when [1] is not null then 'yes' else '' end as [1],
case when [2] is not null then 'yes' else '' end as [2],
case when [3] is not null then 'yes' else '' end as [3],
case when [4] is not null then 'yes' else '' end as [4]
...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks ralmada!
I also caught the Then, and renamed my columns. I noticed that I originally have about 45K records, then when I Pivot, I only have 25.5K.
I noticed that the records are grouped, and that where I might have two records
car fast
car slow
i now have 1
car fast slow
I also caught the Then, and renamed my columns. I noticed that I originally have about 45K records, then when I Pivot, I only have 25.5K.
I noticed that the records are grouped, and that where I might have two records
car fast
car slow
i now have 1
car fast slow
yes, that's the whole purpose of pivot... I'm not sure what you want them, something like this?
Fast Slow
car Yes
car Yes
If so, then you can try
Fast Slow
car Yes
car Yes
If so, then you can try
select rn,
ItemID,
ItemName,
case when [Fast] is not null then 'yes' else '' end as [Fast],
case when [Slow] is not null then 'yes' else '' end as [Slow],
case when [Diesel] is not null then 'yes' else '' end as [Diesel],
case when [Steam] is not null then 'yes' else '' end as [Steam]
from (
select a.ItemID,
a.ItemName,
c.FeatID,
c.FeatName
row_number() over (partition by ItemID order by ItemID) rn
ItemFeature a
inner join Item b on a.ItemID = b.ItemID
inner join Feature c on a.FeatID = c.FeatID
) o
pivot (max(FeatID) for FeatName in ([Fast], [Slow], [Diesel], [Steam])) p
ASKER
Sorry for the late reply. Yes ralmada, that is actually what I wanted, I was just trying to verify the data. I actually have about 10 measures, and I was trying to place the values in a single column and remove the last comma without having to do something like:
case... + ' ' + case... + ' ' + case..., len(case... + ' ' + case... + ' ' + case..) - 1...
case... + ' ' + case... + ' ' + case..., len(case... + ' ' + case... + ' ' + case..) - 1...
Open in new window