Using Pivot in crosstab to transform data

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

Transact-SQL (2008)

Using Pivot in crosstab to transform data

Author

Topic

MrBloom
Starting Member

36 Posts

Posted - 2013-03-13 : 08:08:13

Hi
I have the following data in a spreadsheet type format and I'd like to transform it into more of a database format so that Weight, Height, Size are transformed into one column per each productID and date as you can see in the examples below. Is it possible to use pivot to create a crosstab query to do this? I'm not too familiar with cross tab queries.
Thanks

Before

ProductID date Weight Height Size
1 04/08/1994 3.2 40.3 80.4
1 15/03/1993 4.1 54.8 43.5
1 05/05/1993 6.8 33.5 45.8
2 07/07/2000 5.9 45.9 67.9
3 29/09/1995 8.2 67.8 90.1

After

ProductID date description value
1 04/08/1994 Weight 3.2
1 15/03/1993 Weight 4.1
1 05/05/1993 Weight 6.8
1 04/08/1994 Height 40.3
1 15/03/1993 Height 54.8
1 05/05/1993 Height 33.5
1 04/08/1994 Size 80.4
1 15/03/1993 Size 43.5
1 05/05/1993 Size 45.8
2 07/07/2000 Weight 5.9
2 07/07/2000 Height 45.9
2 07/07/2000 Size 67.9
3 29/09/1995 Weight 8.2
3 29/09/1995 Height 67.8
3 29/09/1995 Size 90.1

MrBloom
Starting Member

36 Posts

Posted - 2013-03-13 : 08:53:09

I managed to solve it with UNPIVOT.

   
Select * from  [MyTable]
 
UNPIVOT ([Value] for [Description] IN ([Weight],[Height],[Size])) unPvt

ORDER BY ProductID, Description

regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources