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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Using Pivot in crosstab to transform data
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MrBloom
Starting Member

United Kingdom
36 Posts

Posted - 03/13/2013 :  08:08:13  Show Profile  Reply with Quote

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

United Kingdom
36 Posts

Posted - 03/13/2013 :  08:53:09  Show Profile  Reply with Quote


I managed to solve it with UNPIVOT.




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

ORDER BY ProductID, Description





regards
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000