Nikhil Jain
New User
Joined: 20 Jul 2011
Posts: 16
Location: India
|
|
|
|
Hi,
You can try writing a query similar to the below given query -
| Code: |
WITH NIK (RNUM,PSTL_ID,CITY_NAME) AS
(
SELECT ROW_NUMBER() OVER(ORDER BY CITY_NAME DESC),
PSTL_ID,CITY_NAME
FROM ABC
WHERE PSTL_ID='123-456'
)
SELECT A.PSTL_ID,A.CITY_NAME
,(
SELECT B.CITY_NAME
FROM NIK B
WHERE B.PSTL_ID='123-456'
AND RNUM=2
) AS COL2
,(
SELECT B.CITY_NAME
FROM NIK B
WHERE B.PSTL_ID='123-456'
AND RNUM=3
) AS COL3
,(
SELECT B.CITY_NAME
FROM NIK B
WHERE B.PSTL_ID='123-456'
AND RNUM=4
) AS COL4
FROM NIK A
WHERE A.PSTL_ID='123-456'
FETCH FIRST 1 ROWS ONLY
; |
NB : You have to be very sure of the maximum no. of records/rows that can be returned against a particular where clause. In my case, I knew for a particular PSTL_ID, there can be max 4 Cities. Hope it helps!! |
|
