How to select top 3 rows and convert the rows to columns for each group

Question

Post reply

How to select top 3 rows and convert the rows to columns for each group

sliu

Old Hand

Points: 329
More actions
September 2, 2010 at 4:18 pm

#237779

I have a table with customerID,caegory,cnt.
I want select the top 3 cnt and category names (order by cnt desc) for each customerID.
And then convert rows to columns for each customer
Now the table looks like this:
customerID, Cat, Cnt
c1, cat1, 10
c1, cat2, 5
c1, cat3, 8
c1, cat4, 3
c1, cat5, 1
c2, cat9, 7
c2, cat10, 5
c3, cat1, 1
The result table:
customerID, CatName1,Cnt1,CatName2,Cnt2,CatName3,Cnt3
c1,cat1,10, cat3,8, cat2,5
c2,cat9,7,cat10,5,,
c3,cat1,1,,,,
Now, I put the the result to a temp table, for each patient I update the temp table, the performance was really ugly.
Any idea?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

declare @test-2 TABLE (customerID char(2), Cat varchar(5), Cnt int);

-- See how you start off by actually creating a table and then

-- inserting the data into it? Your doing this makes it a lot easier

-- for all of us volunteers to help you. So, help us help you.

-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/

-- for more details on how to do all of this.

INSERT INTO @test-2

SELECT 'c1', 'cat1', 10 UNION ALL

SELECT 'c1', 'cat2', 5 UNION ALL

SELECT 'c1', 'cat3', 8 UNION ALL

SELECT 'c1', 'cat4', 3 UNION ALL

SELECT 'c1', 'cat5', 1 UNION ALL

SELECT 'c2', 'cat9', 7 UNION ALL

SELECT 'c2', 'cat10', 5 UNION ALL

SELECT 'c3', 'cat1', 1;

;WITH CTE AS

(

SELECT *,

RN = ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY Cnt DESC)

FROM @test-2

)

SELECT customerID,

CatName1 = MAX(CASE WHEN RN = 1 THEN Cat ELSE NULL END),

CatQty1 = MAX(CASE WHEN RN = 1 THEN Cnt ELSE NULL END),

CatName2 = MAX(CASE WHEN RN = 2 THEN Cat ELSE NULL END),

CatQty2 = MAX(CASE WHEN RN = 2 THEN Cnt ELSE NULL END),

CatName3 = MAX(CASE WHEN RN = 3 THEN Cat ELSE NULL END),

CatQty3 = MAX(CASE WHEN RN = 3 THEN Cnt ELSE NULL END)

FROM CTE

WHERE RN <= 3

GROUP BY customerID;

Please see the CrossTabs and Pivots, Parts 1 and 2 links in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

sliu Old Hand Points: 329 More actions · Answer 2

Thank you so so so much. That's exactly what I want.:-P

I really appreciate your help.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Nicely done, Wayne.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 4

Thanks Jeff. You're a good teacher!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2