Transforming data in table - PIVOT(?)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Transforming data in table - PIVOT(?)

Author

Topic

Wodzu
Yak Posting Veteran

58 Posts

Posted - 2010-04-13 : 08:25:03

Hello experts

I have a table with such structure:

CREATE TABLE dbo.Products
(
  RowID int IDENTITY,
  ProductID int, 
  FeatureName varchar(50),
  FeatureValue varchar(50)
)

After performing SELECT * on it, I have data like this (an example):


1	1	Color  Blue
2	2	Color  Red
3	3	Color  Green
4	1	Weight 2
5	2	Weight 5
6	3	Weight 3.5
7	1	Length 2.43
8	2	Length 4.34
9	3	Length 1.18
10	4	Color  Orange

My question is: Is it possible to transform this data in a fast way (not using cursors etc.) to achive this result:


[ProductID] [Color] [Weight] [Length]
     1       Blue     2        2.43
     2       Red      5        4.34
     3       Green    3.5      1.18
     4       Orange   NULL     NULL

I would like to also join the reuslt with oter table by ProductID. I would like to perform join in one query, I need to be done it fast in terms of performance.

I was thinking about using PIVOT but actually can't make it work...

Thank you for your time.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-04-13 : 08:32:25

[code]Select ProductId,
MAX(Case When FeatureName = Color then FeatureValue Else Null End)as Color,
MAX(Case When FeatureName = Weight then FeatureValue Else Null End)as Weight ,
MAX(Case When FeatureName = Length then FeatureValue Else Null End)as Length
from dbo.Products
Group by ProductId
Order by ProductId[/code]

You can use PIVOT as well.

Wodzu
Yak Posting Veteran

58 Posts

Posted - 2010-04-13 : 08:37:39

Thank you. Could you show me an example with PIVOT? The problem is that I do not know how many Features will be for each product. They might be added dynamically over time. I do not want to have them hardcoded in my query. Is it possible?

Sachin.Nand

2937 Posts

Posted - 2010-04-13 : 08:43:09

[code]

create table #yourTable (FeatureName varchar(20), FeatureValue varchar(20))

insert #yourTable

select 'Color', 'Blue'

union all select 'Color ', 'Red'

union all select 'Color ', 'Green'

union all select 'Weight', '2'

union all select 'Weight', '5'

union all select 'Weight', '3.5'

union all select 'Length', '2.43'

union all select 'Length', '4.34'

union all select 'Length', '1.18'

union all select 'Color', 'Orange'

declare @colList varchar(8000)

select @colList = coalesce(@colList + ', [' + FeatureName + ']', '[' + FeatureName + ']') from #yourTable group by FeatureName

exec('

select ' + @colList + '

from (select FeatureName, FeatureValue, row_number() over (partition by FeatureName order by FeatureValue) as rn from #yourTable) t

pivot (max(FeatureValue) for FeatureName in (' + @colList + ')) as p')

drop table #yourTable

[/code]

PBUH

Wodzu
Yak Posting Veteran

58 Posts

Posted - 2010-04-13 : 08:48:58

Thank you Idera.

Unfortunately that does not give me the expected results. It returns this:


Blue   1.18	2
Green  2.43	3.5
Orange 4.34	5
Red    NULL	NULL

Data is mixed up and there is not ProductID column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-13 : 08:49:12

yup.it is possible. see below

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-04-13 : 08:55:53

Sorry dint check the o/p properly.I hope this is now right.



create table #yourTable (productid int,FeatureName  varchar(20), FeatureValue  varchar(20))



insert #yourTable

select 1,'Color', 'Blue'

union all select 2,'Color', 'Red'

union all select 3,'Color', 'Green'

union all select 1,'Weight', '2'

union all select 2,'Weight', '5'

union all select 3,'Weight', '3.5'

union all select 1,'Length', '2.43'

union all select 2,'Length', '4.34'

union all select 3,'Length', '1.18'

union all select 4,'Color', 'Orange'



declare @colList varchar(8000)

select @colList = coalesce(@colList + ', [' + FeatureName + ']', '[' + FeatureName + ']') from #yourTable  group by FeatureName order by  FeatureName

 

exec('

select productid,' + @colList + '

from   (select productid,FeatureName, FeatureValue, row_number() over (partition by productid,FeatureName order by FeatureValue) as rn from #yourTable) t

pivot (max(FeatureValue) for FeatureName in (' + @colList + ')) as p ')

 



drop table #yourTable

PBUH

Wodzu
Yak Posting Veteran

58 Posts

Posted - 2010-04-13 : 09:05:01

Idera, you rock! My yaw is on the floor ;)

Thanks visakh16 for the link. I've checked it out, the problem is it already goes on the deep waters.

Thanks a lot guys!

Sachin.Nand

2937 Posts

Posted - 2010-04-13 : 09:09:24

quote:
Originally posted by Wodzu

Idera, you rock! My yaw is on the floor ;)

Thanks visakh16 for the link. I've checked it out, the problem is it already goes on the deep waters.

Thanks a lot guys!

Gee thanks for the compliment.I hope now your jaw is at the right place.

PBUH

Wodzu
Yak Posting Veteran

58 Posts

Posted - 2010-04-13 : 09:24:23

Not yet, still evaluating your query ;)

From what sources have you learned working with PIVOT operator?

I've read a lot blogs about it but still I barely understand what is going on there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-13 : 10:04:43

quote:
Originally posted by Wodzu

Not yet, still evaluating your query ;)

From what sources have you learned working with PIVOT operator?

I've read a lot blogs about it but still I barely understand what is going on there.

look into books online for explanation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources