Trying to get max value with column name for each row using pivot?

Question

Trying to get max value with column name for each row using pivot?

sql4us

Old Hand

Points: 388
More actions
September 1, 2011 at 1:09 pm

#256467

I need to get max values for each row and its column name.
For ex:
My table:
Zipcode West East North South
10001 50 10 100 5
10002 100 200 26 180
10003 0 0 0 0
....
Output table:
Zipcode West East North South Maxvalue colname
10001 50 10 100 5 100 North
10002 100 200 26 180 200 East
10003 0 0 0 0 0 N/A
....
Code :
SELECT ZIPCODE,maxvalue,colname
FROM
(
SELECT ZIPCODE, West, East, North, South
FROM dbo.tbl1
) ua
UNPIVOT
(maxvalue for colname in (West, East, North, SouthOTHRACE_CY,RACE2UP_CY)) ub
Results: ( I just want 96708 5884 west, not sure why its pulling all the zipcodes)?
967085884West
9670875East
9670856North
96708847South
96713594West
967133South
967137East
96713140North

Viewing 10 posts - 1 through 9 (of 9 total)

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

Martin Schoombee SSCoach Points: 19128 More actions · Answer 1

If you have a limited number of columns, you could use a case statement. Alternatively, try using a cte to "normalize" the data before aggregating.

sql4us Old Hand Points: 388 More actions · Answer 2

I have 30,000 records..I almost got it but if you saw my code and results it shows all the values for the same zipcode. I just need max value, column name for particular zipcode.

For ex:

96708 5884 West

92113 100 East

etc.,

Martin Schoombee SSCoach Points: 19128 More actions · Answer 3

sql4us (9/1/2011)
I have 30,000 records..I almost got it but if you saw my code and results it shows all the values for the same zipcode. I just need max value, column name for particular zipcode.
For ex:
96708 5884 West
92113 100 East
etc.,

To do this, you need to rank the data after you have normalized it. Try using the Row_Number() function to achieve that.

sql4us Old Hand Points: 388 More actions · Answer 4

Martin Schoombee (9/1/2011)
sql4us (9/1/2011)
I have 30,000 records..I almost got it but if you saw my code and results it shows all the values for the same zipcode. I just need max value, column name for particular zipcode.
For ex:
96708 5884 West
92113 100 East
etc.,
To do this, you need to rank the data after you have normalized it. Try using the Row_Number() function to achieve that.

Thanks a lot for your reply

I tried this code:

SELECT ZIPCODE,maxvalue,colname, row_number() over (partition by ZIPCODE order by ZIPCODE, maxvalue desc) col

FROM

(

SELECT ZIPCODE, West, East, South, North

FROM do_tbl1

) ua

UNPIVOT

(maxvalue for colname in (West, East, South, North)) ub

Results:

zipcode maxvalue colname col

1002228641West 1

100223238 North 2

10022701 South 3

10022507 East 4

1002351931East1

100237348 West 2

100234917South 3

100232402north 4

I am unable to get: Please let me know where I am doing wrong?

zipcode maxvalue colname col

1002228641West 1

1002351931East1

Martin Schoombee SSCoach Points: 19128 More actions · Answer 5

Almost there 🙂

Two things to consider:

1. Your order by clause should only include the maxvalue column and not the zip code.

2. Encapsulate your whole query in a cte, and then select the rows from the cte where the col field = 1

Like so:

;with MyCte

as

(

<your query>

)

select * from MyCte where col = 1

sql4us Old Hand Points: 388 More actions · Answer 6

Martin Schoombee (9/1/2011)
Almost there 🙂
Two things to consider:
1. Your order by clause should only include the maxvalue column and not the zip code.
2. Encapsulate your whole query in a cte, and then select the rows from the cte where the col field = 1
Like so:
;with MyCte
as
(
<your query>
)
select * from MyCte where col = 1

Thank you so much...Now it makes sense. I have one more question

I want to include all column names to display

For ex:

Zipcode West East North South maxvalue Colname

00001 100 10 5 1 100 West

00002 2 88 4 99 99 South

....

Right now it just shows,

Zipcode maxvalue colname

When I include all column names it says invalid column name...not sure if I am missing something..so close...thanks again 🙂

Martin Schoombee SSCoach Points: 19128 More actions · Answer 7

sql4us (9/1/2011)
Thank you so much...Now it makes sense. I have one more question
I want to include all column names to display
For ex:
Zipcode West East North South maxvalue Colname
00001 100 10 5 1 100 West
00002 2 88 4 99 99 South
....
Right now it just shows,
Zipcode maxvalue colname
When I include all column names it says invalid column name...not sure if I am missing something..so close...thanks again 🙂

In that case (and if you have only one record for each zip code), you should rather use a case statement and not the elaborate way of denormalizing and then normalizing again.

Something like this:

selectZipCode

,West

,East

,North

,South

,case

when West >= East and West >= North and West >= South then West

when East >= West and East >= North and East >= South then East

when North >= East and North >= West and North >= South then North

when South >= East and South >= North and South >= West then South

end as MaxValue

from<table>

Edit: At least you've learnt a few valuable techniques here today 🙂

sql4us Old Hand Points: 388 More actions · Answer 8

Thanks a lot for replying and explaining step by step 🙂 Really appreciate:-)

Martin Schoombee SSCoach Points: 19128 More actions · Answer 9

Martin Schoombee

SSCoach

Points: 19128

September 2, 2011 at 6:10 am

#1376391

You are very welcome 🙂