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...

ralmada

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

ralmada

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

ralmada

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

coldchillin

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

coldchillin

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...

ralmada

>>
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

ralmada

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

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

coldchillin

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

ralmada

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

coldchillin

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...