Distribute each 3 rows in one column

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)

Distribute each 3 rows in one column

Author

Topic

cmosii
Starting Member

1 Post

Posted - 2012-07-05 : 05:26:05

Hi ...
I have a table which contains
ROW1 ROW2
ID File
============
1 1.file
2 2.file
3 3.file
4 4.file
5 5.file
6 6.file
7 7.file
8 8.file
============

is it possible to show it in the following format
Row1 Row2 Row3
1.file 2.file 3.file
4.file 5.file 6.file
7.file 8.file

...etc

Thank you for your cooperation

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-05 : 05:47:52

something like

select a1.file, a2.file, a3.file
(select *, seq = id/3 from tbl where id%3 = 1) a1
left join (select *, seq = id/3 from tbl where id%3 = 2) a2
on a2.seq = a1.seq
left join (select *, seq = id/3 from tbl where id%3 = 0) a3
on a3.seq = a1.seq

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 10:06:20

[code]
SELECT MAX(CASE WHEN Rn=1 THEN File END) AS Row1,
MAX(CASE WHEN Rn=2 THEN File END) AS Row2,
MAX(CASE WHEN Rn=3 THEN File END) AS Row3
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY (ID-1)/3 ORDER BY ID) AS Rn,(ID-1)/3 AS Grp,*
FROM Table
)t
GROUP BY Grp
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources