Split a column into 4 - 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)

Split a column into 4

Author

Topic

Elphie
Starting Member

2 Posts

Posted - 2009-07-20 : 06:54:54

I've got a table like this:

ticket col
----------
100 --- 1
104 --- 2
105 --- 3
117 --- 0
121 --- 1
129 --- 2
132 --- 3
155 --- 0
156 --- 1
...

And I need:
|- 1 -|- 2 -|- 3 -|- 0 -|
--------------------------
| 100 | 104 | 105 | 117 |
| 121 | 129 | 132 | 155 |
| 156 | ...

I tried to do as suggested in post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129577


SELECT *
FROM 
(
SELECT ticket,(row_number() over (order by ticket))%4 as col
FROM tickets
WHERE IdUser=4
) tb
pivot
(
max(ticket)
for col in ([1],[2],[3],[0])
)p

but I only get one row:
|- 1 -|- 2 -|- 3 -|- 0 -|
-------------------------
| 190 | 191 | 192 | 197 |

I'm lost ........ any idea?

Thanks!

--------------
Elphie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 07:09:38

[code]DECLARE @Sample TABLE
(
Ticket INT,
Col INT
)

INSERT @Sample
SELECT 100, 1 UNION ALL
SELECT 104, 2 UNION ALL
SELECT 105, 3 UNION ALL
SELECT 117, 0 UNION ALL
SELECT 121, 1 UNION ALL
SELECT 129, 2 UNION ALL
SELECT 132, 3 UNION ALL
SELECT 155, 0 UNION ALL
SELECT 156, 1

SELECT p.[1],
p.[2],
p.[3],
p.[0]
FROM (
SELECT Ticket,
Col,
ROW_NUMBER() OVER (PARTITION BY Col ORDER BY Ticket) AS grpID
FROM @Sample
) AS s
PIVOT (
MAX(s.Ticket)
FOR Col IN ([1], [2], [3], [0])
) AS p[/code]

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 07:10:07

[code]
DECLARE @sample TABLE
(
ticket int,
col int
)
INSERT INTO @sample
SELECT 100, 1 UNION ALL
SELECT 104, 2 UNION ALL
SELECT 105, 3 UNION ALL
SELECT 117, 0 UNION ALL
SELECT 121, 1 UNION ALL
SELECT 129, 2 UNION ALL
SELECT 132, 3 UNION ALL
SELECT 155, 0 UNION ALL
SELECT 156, 1

SELECT [1], [2], [3], [0]
FROM
(
SELECT ticket, col,
row_no = row_number() OVER (ORDER BY ticket, col) / 4
FROM @sample
) s
pivot
(
MAX(ticket)
FOR col IN ([1], [2], [3], [0])
)p
/*
1 2 3 0
----------- ----------- ----------- -----------
100 104 105 NULL
121 129 132 117
156 NULL NULL 155

(3 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 07:26:25

khtan, remember ROW_NUMBER() is 1-based, not 0-based.

row_no = (row_number() OVER (ORDER BY ticket, col)-1) / 4

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 07:38:26

oh yeah .. . thanks

KH
[spoiler]Time is always against us[/spoiler]

Elphie
Starting Member

2 Posts

Posted - 2009-07-20 : 08:11:55

First of all, THANKS A LOT!
Second I have to take a deeper look to how pivot and partition by work. I understand it more or less, but more "less" than "more".

But .... Let's rewind ...

I've got a table with idUser, ticket number.
I'm asked to show for each user a table with 4 columns with their tickets ordered (sometimes is better not to wonder why they ask what they ask...).

So, first step I decided to apply a mod to the row_number so I can have the tickets distributed in 4 groups:

SELECT ticket,(row_number() OVER (ORDER BY ticket))%4 AS Col
FROM tickets
WHERE IdUser=2

So the result is:

SELECT
p.[1],
p.[2],
p.[3],
p.[0]
FROM
(
SELECT v.Ticket, v.Col, (row_number() OVER (PARTITION BY v.Col ORDER BY v.Ticket)) AS grpID
FROM(
SELECT Ticket,
(row_number() OVER (ORDER BY Ticket))%4 AS Col
FROM Tickets
WHERE IdUser=2
)v
) AS s
PIVOT
(
MAX(s.Ticket)
FOR Col IN ([1],[2],[3],[0])
)AS p

With three nested selects .... is it ok? I mean in performance results ...

--------------
Elphie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources