All Forums UDA
Dan 2 posts Joined 05/06
01 Jun 2006
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 YEAR1 John Blue 20001 John Blue 20011 John Blue 20032 Mary Black 19992 Mary Black 20013 Gerry Silver 20023 Gerry Silver 20033 Gerry Silver 20043 Gerry Silver 2005The output must be like the followingID_NO NAME MAKE YEAR1 John Blue 2000,2001,20032 Mary Black 1999,20013 Gerry Silver 2002.2003,2004,2005Any help would be appreciatedMany thanksDan

j355ga 100 posts Joined 12/05
01 Jun 2006

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.

Jeff

DEEPU 9 posts Joined 10/05
03 Jun 2006

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) RNFROM DBC.COLUMNS)AGROUP BY 1,2,3

You must sign in to leave a comment.