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...
LVL 1
coldchillinAsked:
Who is Participating?
 
ralmadaCommented:
and if you want to show the Feature Name as column header instead of the ID then
select 	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
	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

Open in new window

0
 
ralmadaCommented:
try
select 	ItemID,
	ItemName,
	[1],
	[2],
	[3],
	[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

Open in new window

0
 
ralmadaCommented:
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

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ralmadaCommented:
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)

Open in new window

0
 
coldchillinAuthor Commented:
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

0
 
coldchillinAuthor Commented:
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...

0
 
ralmadaCommented:
>>
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

Open in new window

0
 
ralmadaCommented:
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]
...

Open in new window

0
 
coldchillinAuthor Commented:
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
0
 
ralmadaCommented:
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


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

Open in new window

0
 
coldchillinAuthor Commented:
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...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.