How to combine 2 rows into single row in teradata?

by muriel.schmidt , in category: MySQL , 21 days ago

How to combine 2 rows into single row in teradata?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

by filiberto , 20 days ago

@muriel.schmidt 

One way to combine 2 rows into a single row in Teradata is by using the PIVOT function. Here is an example:


If we have a table called "orders" with the following data:


order_id | product_id | order_date


1 | 100 | 2022-01-01


1 | 200 | 2022-01-01


We can combine the two rows for order_id = 1 into a single row like this:


SELECT order_id, max(case when rn = 1 then product_id end) as product_id_1, max(case when rn = 1 then order_date end) as order_date_1, max(case when rn = 2 then product_id end) as product_id_2, max(case when rn = 2 then order_date end) as order_date_2 FROM (SELECT order_id, product_id, order_date, row_number() over (partition by order_id order by product_id) as rn FROM orders) as sub GROUP BY order_id;


This query uses the row_number() function to assign a rank to each row within the partition, and then uses the max() function with a conditional to pivot the data into 2 columns for product_id and order_date.


This query will result in a single row for order_id = 1 with the data from the 2 original rows combined into separate columns.