All the GURU'S out their

UDA
Dan
Fan

All the GURU'S out their

Hi Guys,

I need help with the following please;

I’ve got a table that looks like this,

ID_NO NAME MAKE YEAR
1 John Blue 2000
1 John Blue 2001
1 John Blue 2003
2 Mary Black 1999
2 Mary Black 2001
3 Gerry Silver 2002
3 Gerry Silver 2003
3 Gerry Silver 2004
3 Gerry Silver 2005

The output must be like the following

ID_NO NAME MAKE YEAR
1 John Blue 2000,2001,2003
2 Mary Black 1999,2001
3 Gerry Silver 2002.2003,2004,2005

Any help would be appreciated

Many thanks
Dan
2 REPLIES
Teradata Employee

Re: All the GURU'S out their

If your shop is using V2R6 you could try the WITH RECURSIVE clause to concatenate a column in an arbitrary number of rows into a single string. There is an example in this forum if you look at my posts.

Enthusiast

Re: All the GURU'S out their

If your min year is '1999' & max year is '2005'. That means (2005-1999 = 7) 7 times you can use -
MAX(CASE WHEN RN = x THEN YEAR ELSE '' END)||','||
to achieve your result set.

SEL ID_NO,NAME,MAKE,
MAX(CASE WHEN RN = 1 THEN YEAR ELSE '' END)||','||
MAX(CASE WHEN RN = 2 THEN YEAR ELSE '' END)||','||
MAX(CASE WHEN RN = 3 THEN YEAR ELSE '' END)||','||
MAX(CASE WHEN RN = 4 THEN YEAR ELSE '' END)||','||
MAX(CASE WHEN RN = 5 THEN YEAR ELSE '' END)||','||
MAX(CASE WHEN RN = 6 THEN YEAR ELSE '' END)||','||
MAX(CASE WHEN RN = 7 THEN YEAR ELSE '' END)
FROM
(
SEL ID_NO,NAME,MAKE,YEAR,RANK()OVER(PARTITION BY ID_NO,NAME,MAKE ORDER BY YEAR) RN
FROM DBC.COLUMNS
)A
GROUP BY 1,2,3