CODEWITHSUNDEEP
sqlteradata
kannan m
kannan m

Reputation: 67

How to combine 2 rows into single row in Teradata

I have a resultset in the below-mentioned form returned by a SQL:

ID   Key
1    A 
2    A
3    A

Now my requirement is to show the data in the below form:

Key ID1 ID2 ID3
A   1   2   3

How to build an SQL for this?

Upvotes: 1

Views: 5881

Answers (2)

dnoeth
dnoeth

Reputation: 60462

A Windowed Aggregate based solution with a single STATS-step in Explain:

SELECT
   key,
   -- value from 1st row = current row
   ID AS ID1,
   -- value from next row, similar to LEAD(ID, 1) Over (PARTITION BY Key ORDER BY ID)
   Min(ID) 
   Over (PARTITION BY Key 
         ORDER BY ID 
         ROWS BETWEEN 1 Following AND 1 Following) AS ID2 ,
   -- value from 3rd row
   Min(ID)
   Over (PARTITION BY Key
         ORDER BY ID
         ROWS BETWEEN 2 Following AND 2 Following) AS ID3
FROM mytable
QUALIFY -- only return the 1st row
   Row_Number()
   Over (PARTITION BY key
         ORDER BY ID) = 1

Upvotes: 2

zarruq
zarruq

Reputation: 2465

As teradata 14.10 doesn't have a PIVOT function and assuming that for every unique key, there will be no more than 3 IDs( as mentioned in comments), you can use row_number() and aggregate function as below to get your desired result.

SELECT
  key1,
  MAX(CASE WHEN rn = 1 THEN ID END) AS ID1,
  MAX(CASE WHEN rn = 2 THEN ID END) AS ID2,
  MAX(CASE WHEN rn = 3 THEN ID END) AS ID3
  FROM 
  (SELECT
  t.*,
  ROW_NUMBER() OVER (PARTITION BY key1 ORDER BY ID) AS rn
  FROM table1 t) t
  GROUP BY key1;

Result:

+------------+-----+-----+-----+
|    key1    | id1 | id2 | id3 |
+------------+-----+-----+-----+
| A          |   1 |   2 |   3 |
+------------+-----+-----+-----+

DEMO

Upvotes: 0

Related Questions