Converting three Rows to one row

Question

I'm trying to convert Rows to Columns from a MS SQL table...

my MS SQL table is like...

I want to SELECT the output as below... I tried with pivot tables and cross join.. unfortunately could not make it.

any help is highly appreciated

Try to use example provide here https://stackoverflow.com/questions/27712374/how-to-convert-multiple-rows-to-one-row-in-sql-server — Eric Aime Tchatchoua, Oct 08 '19 at 12:56

score 2 · Accepted Answer · answered Oct 08 '19 at 13:00

You can use ROW_NUMBER() in a subquery to rank records, and the do conditional aggregation in the outer query:

SELECT 
    id,
    SubId,
    MAX(CASE WHEN rn = 1 THEN code END) Code1,
    MAX(CASE WHEN rn = 1 THEN TotalAmount END) Code1TotalAmount,
    MAX(CASE WHEN rn = 1 THEN TotalDays END) Code1TotalDays,
    MAX(CASE WHEN rn = 2 THEN code END) Code2,
    MAX(CASE WHEN rn = 2 THEN TotalAmount END) Code2TotalAmount,
    MAX(CASE WHEN rn = 2 THEN TotalDays END) Code2TotalDays,
    MAX(CASE WHEN rn = 3 THEN code END) Code3,
    MAX(CASE WHEN rn = 3 THEN TotalAmount END) Code3TotalAmount,
    MAX(CASE WHEN rn = 3 THEN TotalDays END) Code3TotalDays
FROM (
    SELECT
        t.*,
        ROW_NUMBER() OVER(PARTITION BY ID, SubId ORDER BY code) rn
    FROM mytable t
) x
GROUP BY ID, SubId

Converting three Rows to one row

1 Answers1