Mod Function - 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

General SQL Server Forums

New to SQL Server Administration

Mod Function

Author

Topic

bhy
Starting Member

1 Post

Posted - 2010-09-22 : 14:26:12

Hi there,

If I have a table with a colum like below

[ID
1
2
3
4
5
6
7
8

I want to display it like

1 2 3 4
5 6 7 8

The code below is how I attempted to do this, any help would be greatly appreciated. I tried using the mod of the id and put it in a seperate column accordingly.

select

(select ID from test_table where (ID % 2) <> 0
and (ID % 3) <> 0
and (ID % 4) <> 0
) as 'Col1',

(select ID from test_table where (ID % 2) = 0
and (ID % 3) <> 0
and (ID % 4) <> 0
) as 'Col2',

(select ID from test_table where
(ID % 3) = 0
and (ID % 4) <> 0
) as 'Col3',

(select ID from test_table where
(ID % 4) = 0
) as 'Col4'

from test_table

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-22 : 15:07:28

This should do the trick:

select [1] a, [2] b, [3] c, [0] d
from (select (ID-1)/4 d, ID%4 m, ID from test_table) a
pivot (max(ID) for m in ([0],[1],[2],[3])) b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources