Sachu Sachu - 4 months ago 15
SQL Question

how to select the values in the table in this format

I have a table

table1


Item_Name | Type | Qty | Amount

A Like 2 10
B Love 1 20
C Ours 1 10
A Love 1 10
A Like 1 10
B Ours 1 10


I need a o/p as below

Item_Name Like | Love | Ours Like | Love | Ours
Amount Qty

A 20 10 0 3 1 0
B 0 20 10 0 1 1
C 0 0 10 0 0 1


How can i achieve the desired o/p. I am not able to achieve the same. Please help

Answer

One approach is to use grouping and conditional aggregates to get the output that you need:

SELECT Item_Name,
    SUM(Case WHEN Type='Like' THEN Amount ELSE 0 END) AS Like_Amount,
    SUM(Case WHEN Type='Love' THEN Amount ELSE 0 END) AS Love_Amount,
    SUM(Case WHEN Type='Ours' THEN Amount ELSE 0 END) AS Ours_Amount,
    SUM(Case WHEN Type='Like' THEN Qty ELSE 0 END) AS Like_Qty,
    SUM(Case WHEN Type='Love' THEN Qty ELSE 0 END) AS Love_Qty,
    SUM(Case WHEN Type='Ours' THEN Qty ELSE 0 END) AS Ours_Qty
FROM table1
GROUP BY Item_Name
ORDER BY Item_Name

Above query works if list of possible values in Type column is fixed. If these values can change, you need to use other approaches (e.g. PIVOT)

Comments