Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

PIVOT Question - Select row values as columns

Posted on 2011-05-11
11
Medium Priority
?
293 Views
Last Modified: 2012-06-27
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...
0
Comment
Question by:coldchillin
  • 7
  • 4
11 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 35740091
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
 
LVL 41

Expert Comment

by:ralmada
ID: 35740096
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
 
LVL 41

Expert Comment

by:ralmada
ID: 35740153
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
LVL 1

Author Comment

by:coldchillin
ID: 35740298
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
 
LVL 1

Author Comment

by:coldchillin
ID: 35740398
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
 
LVL 41

Expert Comment

by:ralmada
ID: 35740489
>>
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
 
LVL 41

Expert Comment

by:ralmada
ID: 35741298
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
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 35741332
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
 
LVL 1

Author Comment

by:coldchillin
ID: 35741987
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
 
LVL 41

Expert Comment

by:ralmada
ID: 35742617
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
 
LVL 1

Author Comment

by:coldchillin
ID: 35747967
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Loops Section Overview
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question