0

This question already has an answer here:

I need query of looping in stored procedure for table like this:

Table Matrix Report

| Nama  | Motor   |
|-------|---------|
| Abdul | Vario   |
| Abdul | Beat    |
| Mei   | Supra   |
| Leli  | Supra   |
| Budi  | Megapro |
| Ali   | CB150   |

Output :

| Nama   | Vario | Beat | Supra | Megapro | CB150 |
|--------|-------|------|-------|---------|-------|
| Abdul  | 1     | 1    | 0     | 0       | 0     |
| Mei    | 0     | 0    | 1     | 0       | 0     |
| Leli   | 0     | 0    | 1     | 0       | 0     |
| Budi   | 0     | 0    | 0     | 1       | 0     |
| Ali    | 0     | 0    | 0     | 0       | 1     |

I need dynamic query because if I added new data, they automatically sum up.

marked as duplicate by Paul White Nov 10 '16 at 16:19

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2

Actually you don't need a dynamic query, you need a pivot query:

SELECT Nama,
SUM(CASE WHEN Motor = 'Vario' THEN 1 ELSE 0 END) as Vario,
SUM(CASE WHEN Motor = 'Beat' THEN 1 ELSE 0 END) as Beat,
SUM(CASE WHEN Motor = 'Supra' THEN 1 ELSE 0 END) as Supra,
SUM(CASE WHEN Motor = 'Megapro' THEN 1 ELSE 0 END) as Megapro
SUM(CASE WHEN Motor = 'CB150' THEN 1 ELSE 0 END) as CB150
FROM MatrixReport
GROUP BY Nama;

This query will automatically sum up whether new rows are added or not.

This is a SQL query, not an SP. You need to learn more about how to create a stored procedure. Take a look at these Stack Overflow Q & As to help you in creating your first SP:

You should also learn more about GROUP_CONCAT function in MySQL:

Not the answer you're looking for? Browse other questions tagged or ask your own question.