Sachu Sachu - 1 year ago 68
SQL Question

how to select the values in the table in this format

I have a table


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 Source

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)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download