Teradata-Combine multiple rows of a column to multiple columns in a row

General

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

2 REPLIES
Enthusiast

Re: Teradata-Combine multiple rows of a column to multiple columns in a row

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;

Junior Contributor

Re: Teradata-Combine multiple rows of a column to multiple columns in a row

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.