All Forums General
menka_An 1 post Joined 01/15
29 Jan 2015
Teradata-Combine multiple rows of a column to multiple columns in a row

Hello Readers,
I need a solution for the below requirement. It would be of great help if someone can help me on this.
I have a source table which has similar data below. I need to pick the top 4 recently modified rows and combine them into multiple columns in a row. Below is the source and the expected target.
Source:
PK      Name     Department        salary       Date
20       ABC        Sales                 1000       10-Jan-2014
21       ABC      Marketing             2000       05-Jun-2014
24       ABC      Finance                2500       11-sep-2014
26       ABC      HR                       3000       23-Dec-2014
27       ABC      Payroll                 4000        26-Dec-2013
.
.
.
.
 
Target:
Name     Department1   salary1     Department2   salary2       department3   salary3    Department4    salary4
ABC         HR                 3000        Finance            2500           Marketing      2000         sales              1000
 

Raja_KT 1246 posts Joined 07/09
30 Jan 2015

One way is you can try something like this and use substring to get each field and give an alias name:
select name1,regexp_replace(tdstats.udfconcat(trim(dept1)||','||trim(salary)),'"','',1,0,'i) from 
(select name1,dept1,salary,row_number()over(partition by name1 order by dt desc) rn from your_tablevvvvvv qualify row_number() over(partition by name1 order by dt desc)<=4)a group by 1;

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
31 Jan 2015

Two approaches come to mind:
Old style MAX(CASE) over a ROW_NUMBER:

SELECT 
   NAME
   ,MIN(CASE WHEN rn = 1 THEN Department end)
   ,MIN(CASE WHEN rn = 1 THEN salary end)
   ,MIN(CASE WHEN rn = 2 THEN Department end)
   ,MIN(CASE WHEN rn = 2 THEN salary end)
   ,MIN(CASE WHEN rn = 3 THEN Department end)
   ,MIN(CASE WHEN rn = 3 THEN salary end)
   ,MIN(CASE WHEN rn = 4 THEN Department end)
   ,MIN(CASE WHEN rn = 4 THEN salary end)
FROM 
 (
   SELECT salary, department, NAME,
      ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY dt DESC) AS rn
   FROM tab
   QUALIFY rn <= 4 
 ) AS dt
GROUP BY 1

 
Or as you already need a ROW_NUMBER you might avoid the aggregation by using additional OLAP functions:

SELECT 
   NAME
   ,Department
   ,salary
   ,MIN(Department) OVER (PARTITION BY NAME ORDER BY dt DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
   ,MIN(salary) OVER (PARTITION BY NAME ORDER BY dt DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
   ,MIN(Department) OVER (PARTITION BY NAME ORDER BY dt DESC ROWS BETWEEN 2 FOLLOWING AND 2 FOLLOWING)
   ,MIN(salary) OVER (PARTITION BY NAME ORDER BY dt DESC ROWS BETWEEN 2 FOLLOWING AND 2 FOLLOWING)
   ,MIN(Department) OVER (PARTITION BY NAME ORDER BY dt DESC ROWS BETWEEN 3 FOLLOWING AND 3 FOLLOWING)
   ,MIN(salary) OVER (PARTITION BY NAME ORDER BY dt DESC ROWS BETWEEN 3 FOLLOWING AND 3 FOLLOWING)
FROM tab
QUALIFY 
   ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY dt DESC) = 1 
;

If the number of rows per name is low this will outperform the aggregation.
 

Dieter

You must sign in to leave a comment.