LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » T-SQL Problem

T-SQL Problem

Options

Previous Topic · Next Topic

PaulGr

Posted: Wednesday, September 17, 2008 10:39:59 AM

Rank: Newbie

Joined: 1/11/2008
Posts: 5
Points: 15
Where do you live?: Yorkshire, England

Does anyone know of transform the following rows in t-sql

Product DayofWeek Value1 Value2
a Sunday 10 20
a Monday 11 21
a Tuesday 12 22
a Wednesday 13 23
a Thursday 14 24
a Friday 15 25
a Saturday 16 26
b Sunday 50 60
b Monday 51 61
b Tuesday 52 62
b Wednesday 53 63
b Thursday 54 64
b Friday 55 65
b Saturday 56 66

into this result set

Product SunVal1 Sunval2 MonVal1 MonVal2 TuesVal1 TuesVal2 WedVAl1 WedVal2 Thurval1 ThVal2 FriVal1 FriVal2 Satval1 Satval2

a 10 20 11 21 12 22 13 23 14 24 15 25 16 26
b 50 60 51 61 52 62 53 63 54 64 55 65 56 66

Any help would be greatly appreciated

Thanks

bklr

Posted: Tuesday, January 27, 2009 11:32:34 PM

Rank: PFY

Joined: 1/21/2009
Posts: 27
Points: 81
Where do you live?: India

use pivot function for this

Scott Whigham

Posted: Friday, February 06, 2009 4:10:09 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

bklr is right - PIVOT (for SQL 2005/2008) is best for this. For info, check out http://www.learntransactsql.com/VideoTutorials/TSQL-Training-Video/395/Using-PIVOT-Operator-An-introduction-to-PIVOT-Tables.aspx

If you are using SQL Server 2000, then you'll want to use cross tab queries

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.