Max value and its colname using Unpivot in sql? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Max value and its colname using Unpivot in sql?

Author

Topic

sql4us
Starting Member

24 Posts

Posted - 2011-09-01 : 14:43:57

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
....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 00:06:41

[code]
SELECT p.Zipcode,
p.West,
p.East,
p.North,
p.South,
q.Val AS Maxvalue,
q.Direction AS colname
FROM Table p
INNER JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY ZipCode ORDER BY Val DESC) AS Seq,
ZipCode,Direction,Val
FROM
Table t
UNPIVOT (val FOR Direction IN (West,East,North,South))u
)q
ON q.ZipCode=p.ZipCode
AND q.Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sql4us
Starting Member

24 Posts

Posted - 2011-09-02 : 07:58:53

quote:
Originally posted by visakh16

SELECT p.Zipcode,
p.West,
p.East,
p.North,
p.South,
q.Val AS Maxvalue,
q.Direction AS colname
FROM Table p
INNER JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY ZipCode ORDER BY Val DESC) AS Seq,
ZipCode,Direction,Val
FROM
Table t
UNPIVOT (val FOR Direction IN (West,East,North,South))u
)q
ON q.ZipCode=p.ZipCode
AND q.Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks a lot visakh16...Your query is simple and easy to understand and got the results..Thanks a lot :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 08:02:39

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources