pivot a table with multiple rows per record? - 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

General SQL Server Forums

New to SQL Server Programming

pivot a table with multiple rows per record?

Author

Topic

mtcoder
Starting Member

19 Posts

Posted - 2011-08-26 : 09:32:16

I have a table that I need to pivot, noting too bad, but there are numerous rows per record.

example:
ticket sequence lat1 long1 lat2 long2 lat 3 long 3
A1001 1 37.2 140 37.1 139 37.4 139.1
A1001 2 37.1 140.2 37.2 138 37.5 139.2
A1001 3 37.3 140.1 38 138.1 36.6 139.0

What I need to get out is a list of lat longs so my results need to be

ticket lat long
A1001 37.2 140
A1001 37.1 139
A1001 37.4 139.1

Etc for all of the coordinates. Thoughts?
Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-26 : 09:40:24

[code]SELECT p.ticket,p.lat,q.long
FROM
(
SELECT ticket,sequence,lat,ROW_NUMBER() OVER (PARTITION BY ticket,sequence ORDER BY Cat) AS Seq
FROM
(
SELECT ticket,sequence,lat1,lat2,lat3
FROM Table
)t
UNPIVOT (lat FOR cat IN (lat1,lat2,lat3))u
)p
INNER JOIN
(
SELECT ticket,sequence,long,ROW_NUMBER() OVER (PARTITION BY ticket,sequence ORDER BY Cat) AS Seq
FROM
(
SELECT ticket,sequence,long1,long2,long3
FROM Table
)t1
UNPIVOT (long FOR cat IN (long1,long2,long3))u1
)q
ON p.ticket=q.ticket
AND p.sequence=q.sequence
AND p.Seq=q.Seq
[/code]

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

mtcoder
Starting Member

19 Posts

Posted - 2011-08-30 : 15:14:02

much thanks, and one last favor. Where would a where clause fit to filter the ticket to a particular ticket?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-31 : 00:22:53

[code]
SELECT p.ticket,p.lat,q.long
FROM
(
SELECT ticket,sequence,lat,ROW_NUMBER() OVER (PARTITION BY ticket,sequence ORDER BY Cat) AS Seq
FROM
(
SELECT ticket,sequence,lat1,lat2,lat3
FROM Table
WHERE ticket =<some ticket criteria>
)t
UNPIVOT (lat FOR cat IN (lat1,lat2,lat3))u
)p
INNER JOIN
(
SELECT ticket,sequence,long,ROW_NUMBER() OVER (PARTITION BY ticket,sequence ORDER BY Cat) AS Seq
FROM
(
SELECT ticket,sequence,long1,long2,long3
FROM Table
WHERE ticket =<some ticket criteria>
)t1
UNPIVOT (long FOR cat IN (long1,long2,long3))u1
)q
ON p.ticket=q.ticket
AND p.sequence=q.sequence
AND p.Seq=q.Seq

[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources