How to combine 2 rows into single row in teradata?

@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.