How to split IDs across three columns

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

General SQL Server Forums

New to SQL Server Programming

How to split IDs across three columns

Author

Topic

debugx
Starting Member

1 Post

Posted - 2011-06-22 : 01:49:52

Hello all,
For example I have a table:

declare @t table(n int)
insert into @t(n) values (2), (3),(4),(5)

Now I want to convert it to the new table with three columns:

quote:
null 2 3
4 5 null

i.e. it goes in order and if some number is missing null value is used.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-22 : 07:57:55

You can use NTILE function to do this if you are on SQL 2005 or higher. Look for Peso's reply in this posting, he has some sample data and an exmple: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=158923

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-22 : 08:06:58

Another way:

SELECT MAX(CASE WHEN n%3=1 THEN n END) One, MAX(CASE WHEN n%3=2 THEN n END) Two, MAX(CASE WHEN n%3=0 THEN n END) Three
FROM @t GROUP BY (n-1)/3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources