SQL Server Forums
Profile | Register | 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		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MrBloom
Starting Member

United Kingdom
4 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
4 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000