Archives of the TeradataForum

Message Posted: Wed, 14 May 2008 @ 19:11:00 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Schabek,

Some thing like this, should get you what you want. The output should look as follows:

Thanks!

     PARENT_ID ---Data1-----Data2-------        Data3--Data4----Data5----
         Data6-----TCount
     100        05|2004-01-01        04|2003-12-01        03|2003-11-01
       02|2002-12-01        01|2002-11-01                  5
     200        03|2003-11-01        02|2002-12-01        01|2002-11-01
                                                                     3


     sel
     parent_ID
     ,Max(
            Case
               When rnk = 1
                  Then prod_ID|| '|' || INSTALLED_DATE
                  Else '  '
                  end) As Data1
     ,Max(
            Case
               When rnk = 2
                  Then prod_ID  || '|' ||  INSTALLED_DATE
                  Else '  '
                  end) As Data2
     ,Max(
            Case
               When rnk = 3
                  Then prod_ID || '|' ||  INSTALLED_DATE
                  Else '  '
                  end) As Data3
     ,Max(
            Case
               When rnk =4
                  Then prod_ID  || '|' ||  INSTALLED_DATE
                  Else '  '
                  end) As Data4
     ,Max(
            Case
               When rnk = 5
                  Then prod_ID  || '|' ||   INSTALLED_DATE
                  Else '  '
                  end) As Data5
     ,Max(
            Case
               When rnk = 6
                  Then prod_ID  || '|' ||   INSTALLED_DATE
                  Else '  '
                  end) As Data6
       , COUNT(PROD_ID) AS TCount

     From
     (
     sel
     PARENT_ID
     ,PROD_ID
     ,INSTALLED_DATE  (char(10))
     ,rank(INSTALLED_DATE  desc) AS RNK
     From
     example
     GROUP BY  1
     ) dt
     Group By 1
     Order By 1,2
     ;

	<Prev	Next>		<<First	<Prev	Next>	Last>>