| Author |
 Topic  |
|
|
mtcoder
Starting Member
19 Posts |
 Posted - 08/26/2011 : 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
India
47173 Posts |
Posted - 08/26/2011 : 09:40:24
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
mtcoder
Starting Member
19 Posts |
Posted - 08/30/2011 : 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
India
47173 Posts |
Posted - 08/31/2011 : 00:22:53
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
pivot a table with multiple rows per record?
