April 9, 2009 at 9:12 am
Hi, guys,
I am working on a project which need to transfer data from a table to another one. There is a problem I can't figure out: in the source table, like following:
PID TaskNo
001 1002
001 1004
001 1008
001 1256
002 1256
002 1102
002 1009
003 0254
003 2698
...
In this table, each person ID (PID) has more than 1 TaskNo associated with it. Now I want the target table looks like:
PID TaskNo1 TaskNo2 TaskNo3 TaskNo4 TaskNo5
001 1002 1004 1008 1256
002 1256 1102 1009
003 0254 2698
This means I want pick up up to 5 TaskNo from source and make each taskNo as one column. If a PID has more than 5 TaskNo, the others will be discarded; if less then 5, the value will be empty.
This is already totally confuse me. Any suggestion is highly appreciated. Thanks a lot in advance.
April 9, 2009 at 9:20 am
Do you have any particular way of deciding which five to keep if there are six or more? Or just pick randomly?
One way to do this kind of thing is a pivot, but I find it easier to build the thing myself:
;WITH CTE(Row, PID, TaskNo)
AS (SELECT
row_number() OVER (PARTITION BY PID ORDER BY TaskNo),
PID,
TaskNo
FROM
dbo.MyTable)
SELECT
C1.PID,
C1.TaskNo,
C2.TaskNo,
C3.TaskNo,
C4.TaskNo,
C5.TaskNo
FROM
CTE C1
LEFT OUTER JOIN CTE C2
ON C1.PID = C2.PID
AND C2.Row = 2
LEFT OUTER JOIN CTE C3
ON C1.PID = C3.PID
AND C3.Row = 3
LEFT OUTER JOIN CTE C4
ON C1.PID = C4.PID
AND C4.Row = 4
LEFT OUTER JOIN CTE C5
ON C1.PID = C5.PID
AND C5.Row = 5
WHERE
C1.Row = 1 ;
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2009 at 1:14 pm
Thanks a lot for your help. In my case, I just need first 5 rows to pivot to columns. I will try your query script. I'm not quite familiar about sql script: what the ";with" in sql query means? Thanks again.
April 9, 2009 at 1:18 pm
";WITH" sets up a "CTE" (Common Table Expression). I used that to put a Row value into the data, so I could query that in my Where and Join clauses.
Basically, it's a query before the main query. Books Online has all the details on how they work and what they can and can't do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2009 at 1:33 pm
Thank you so much, man! I will try to write a query based on this. I will let you know if this works or not.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply