Selecting rows into columns - SQL Server Forums

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)

Selecting rows into columns

Author

Topic

Doranimal
Starting Member

2 Posts

Posted - 2011-07-15 : 05:03:47

Hi,

I’m not sure you can even do this and as I’m very much a novice at SQL I’ve no idea where to start? I have a table which is structured in the following way

partnumber operation description
111 5 Laser cut
111 10 Fold
111 20 Weld
222 10 Drill
222 20 Tap
333 10 Laser Cut
333 15 Fold
333 20 Tap
333 21 Weld
444 10 Laser Cut

As you can see each part can have one or more operations against it. The numbers for each operation are generated by users and as things change new operations can be inserted anywhere in the sequence. What I’d like to do is return the data dynamically in the following format.

Partnumber op1 desc1 op2 desc2 op3 desc3 op4 desc4
111 5 Laser Cut 10 Fold 20 Weld NULL NULL
222 10 Drill 20 Tap NULL NULL NULL NULL
333 10 Laser Cut 15 Fold 20 Tap 21 Weld
444 10 Laser Cut NULL NULL NULL NULL NULL NULL

I’ve sort of worked out you could do some kind of union on the min and max operation numbers, but fail to see how I can get the numbers in between those values?

Many thanks in advance

Matt

parody
Posting Yak Master

111 Posts

Posted - 2011-07-15 : 08:27:08

You need to pivot it, have a look here:

[url]http://msdn.microsoft.com/en-us/library/ms177410.aspx[/url]

Or write the case statement manually if it needs to be backward compatible.

Doranimal
Starting Member

2 Posts

Posted - 2011-07-27 : 07:25:58

That got me pointed in the right direction. It's all sorted as I want it now. Thank you very much for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources