Hi,
I have a table t1 having data in below foramt

code desc rate date
***** ***** ***** *****
abc abcd 1.2 2004-12-23
ax adbcf .12 2004-12-23
abc abcd 1.19 2004-12-24
abc abcd 1.21 2004-12-25
ax adbcf .11 2004-12-25

I need the output from this table like below
Output
*******
code desc 2004-12-23 2004-12-24 2004-12-25
**** ***** ********** ********** **********
abc abcd 1.2 1.19 1.21
ax adbcf .12 .11

that means I need rates of each code for all date in a single row and column header to display the rates should be date.
Please help me in this regard.

See PIVOT in "SQL on Fire! Part 1"

http://sirdug.org/downloads/SQLonFire_1_SirDUG.pdf

Hi Tonkuma,
I used one of your example like below
WITH
Add_rn1 AS (
SELECT date
, ROWNUMBER() OVER(ORDER BY date) rn1 from
(SELECT DISTINCT R.RT_DT FROM CRNCY_RT R, CRNCY C WHERE R.CRNCY_CD=C.CRNCY_CD AND R.RTDT between '2004-1-1' AND '2004-1-2' AND R.ID=0 AND R.STRTDT<='2004-1-2' AND R.ENDDT>'2004-1-2' AND R.STSCD='O') Q(date))
,
Add_rn2 AS (
SELECT R.CRNCY_CD, C.DESC, R.CRNCY_CD1, R.RT, rn1
, ROWNUMBER() OVER(PARTITION BY R.RT_DT ORDER BY R.CRNCY_CD) rn2
FROM CRNCY_RT R, CRNCY C
, Add_rn1 n
WHERE R.CRNCY_CD=C.CRNCY_CD AND R.RTDT = n.date
AND C.PID=0 AND C.STRTDT<=n.date AND C.ENDDT>n.date AND C.STSCD='O' )
,
Recurse (ln, rn1, CRNCY_CD, DESC, CRNCY_CD1, RT) AS (
SELECT 1 ln
, rn1,CRNCY_CD,DESC,CRNCY_CD1,RT
FROM Add_rn2
WHERE rn2 = 1
UNION ALL
SELECT pre.ln + 1
, pre.rn1
, new.CRNCY_CD
,new.DESC
,new.CRNCY_CD1
, new.RT
FROM Recurse pre
, Add_rn2 new
WHERE pre.ln < 10000
AND pre.rn1 = new.rn1
AND new.rn2 = pre.ln + 1
)

SELECT col2 AS " ", col3 AS " "
FROM (
SELECT -1 ln
, MAX(CASE rn1 WHEN 1 THEN CHAR(date,USA) END)
, MAX(CASE rn1 WHEN 2 THEN CHAR(date,USA) END)
FROM Add_rn1
UNION ALL
SELECT ln,CRNCY_CD,DESC,CRNCY_CD1
, MAX(CASE rn1 WHEN 1 THEN char(RT) END)
, MAX(CASE rn1 WHEN 2 THEN char(RT) END)
FROM Recurse
GROUP BY ln

) Q (ln, col2, col3)
ORDER BY ln;

From Recurse I am selecting 3 more columns CRNCY_CD,DESC,CRNCY_CD1. We are using UNION ALL at the end to get the final result. But I am not able to find out how to add these 3 columns in the 1st table (Add_rn1) of UNION part. Please help me regarding this.

Thanks a lot.

You may want to add strings of column names, like...
'CRNCY_CD' AS CRNCY_CD, 'DESC' AS DESC, 'CRNCY_CD1' AS CRNCY_CD1

Anyway, I thought that you don't need to use such complex query.
How about like this?

Thanks a lot
I tried to execute the query, but I am getting below error

42601(-104)[IBM][CLI Driver][DB2] SQL0104N An unexpected token "<EMPTY>" was found following "<EMPTY>". Expected tokens may include: "CORRELATION NAME". SQLSTATE=42601

Please help in this regard.

Are you using older DB2 version?

Nested table expression without CORRELATION NAME was supported from DB2 9.5 for LUW.

If you are using DB2 9.1 for LUW or earlier,
try