April 10, 2015 at 1:37 pm
Hello Friends,
I need some help with the following data. The table has three columns with PERSON, ACCOUNT and PERCENTAGE.
I would like to grab the top 4 rows for each PERSON and then display the result in one row with the highest PERCENTAGE first, then next and so on.
If a PERSON doesn't have four more or less records then it would just return a NULL in the respective columns.
Thank you all in advance..
SAMPLE DATA
WITH SampleData (PERSON, ACCOUNT, PERCENTAGE) AS
(
SELECT 1125,'02142687841101',44.513
UNION ALL SELECT 1125,'02143657241101',16.502
UNION ALL SELECT 1125,'02143655941101',15.868
UNION ALL SELECT 1125,'02142688081101',14.935
UNION ALL SELECT 1125,'02142688061101',5.958
UNION ALL SELECT 1125,'02142688141101',2.224
UNION ALL SELECT 2236,'02145655621101',90.000
UNION ALL SELECT 2236,'02145657241101',7.643
UNION ALL SELECT 2236,'02145655941101',2.357
UNION ALL SELECT 3347,'02174657241101',60.000
UNION ALL SELECT 3347,'02174655941101',40.000
UNION ALL SELECT 4458,'02141684811101',67.362
UNION ALL SELECT 4458,'02141688081101',16.319
UNION ALL SELECT 4458,'02141688141101',16.319
UNION ALL SELECT 5569,'00560066171001',100.000
)
SELECT *
FROM SampleData;
CURRENT RESULTS
PERSONACCOUNT PERCENTAGE
11250214268784110144.513
11250214365724110116.502
11250214365594110115.868
11250214268808110114.935
1125021426880611015.958
1125021426881411012.224
22360214565562110190.000
2236021456572411017.643
2236021456559411012.357
33470217465724110160.000
33470217465594110140.000
44580214168481110167.362
44580214168808110116.319
44580214168814110116.319
556900560066171001100.000
DESIRED RESULTS
PERSONACCOUNT1PERCENTAGE1ACCOUNT2PERCENTAGE2ACCOUNT3PERCENTAGE3ACCOUNT4PERCENTAGE4
11250214268784110144.513 0214365724110116.502 0214365594110115.868 0214268808110114.935
22360214565562110190.000 021456572411017.643 021456559411012.357 NULL NULL
33470217465724110160.000 0217465594110140.000 NULL NULL NULL NULL
44580214168481110167.362 0214168808110116.319 0214168814110116.319 NULL NULL
556900560066171001100.000 NULL NULL NULL NULL NULL NULL
April 10, 2015 at 1:56 pm
Basically, you need a cross tabs solution with an additional row_number to define the order of your columns.
Here's an article on cross tabs: http://www.sqlservercentral.com/articles/T-SQL/63681/
And here's an example:
WITH SampleData (PERSON, ACCOUNT, PERCENTAGE) AS
(
SELECT 1125,'02142687841101',44.513
UNION ALL SELECT 1125,'02143657241101',16.502
UNION ALL SELECT 1125,'02143655941101',15.868
UNION ALL SELECT 1125,'02142688081101',14.935
UNION ALL SELECT 1125,'02142688061101',5.958
UNION ALL SELECT 1125,'02142688141101',2.224
UNION ALL SELECT 2236,'02145655621101',90.000
UNION ALL SELECT 2236,'02145657241101',7.643
UNION ALL SELECT 2236,'02145655941101',2.357
UNION ALL SELECT 3347,'02174657241101',60.000
UNION ALL SELECT 3347,'02174655941101',40.000
UNION ALL SELECT 4458,'02141684811101',67.362
UNION ALL SELECT 4458,'02141688081101',16.319
UNION ALL SELECT 4458,'02141688141101',16.319
UNION ALL SELECT 5569,'00560066171001',100.000
)
,cteROWS AS(
SELECT *,
ROW_NUMBER() OVER( PARTITION BY PERSON ORDER BY PERCENTAGE DESC) rn
FROM SampleData
)
SELECT PERSON,
MAX( CASE WHEN rn = 1 THEN ACCOUNT END) AS ACCOUNT1,
MAX( CASE WHEN rn = 1 THEN PERCENTAGE END) AS PERCENTAGE1,
MAX( CASE WHEN rn = 2 THEN ACCOUNT END) AS ACCOUNT2,
MAX( CASE WHEN rn = 2 THEN PERCENTAGE END) AS PERCENTAGE2,
MAX( CASE WHEN rn = 3 THEN ACCOUNT END) AS ACCOUNT3,
MAX( CASE WHEN rn = 3 THEN PERCENTAGE END) AS PERCENTAGE3,
MAX( CASE WHEN rn = 4 THEN ACCOUNT END) AS ACCOUNT4,
MAX( CASE WHEN rn = 4 THEN PERCENTAGE END) AS PERCENTAGE4
FROM cteROWS
GROUP BY PERSON;
April 10, 2015 at 4:30 pm
Hi Luis,
This is exactly what I needed..
Thank you Sir, you are awesome.
I will study up the article to referred me to and once again I really appreciate the help.
Have a wonderful weekend !!
DZA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply