Hi I would like to get data from date for users. I ve got a table with all months but i would like to get how much they earn on month
| user | month | money |
|---|---|---|
| 1 | january | 10 |
| 2 | january | 1 |
| 1 | april | 100 |
| 2 | april | 1000 |
| 1 | march | 0 |
| 2 | march | 1 |
And result should be:
| user | money_on_april | money_on_march |
|---|---|---|
| 1 | 100 | 0 |
| 2 | 1000 | 1 |
| 3 | 0 | 0 |
CodePudding user response：
Assuming you want a column for every month, or a certain subset of months:
SELECT
user,
SUM(CASE month WHEN 'january' THEN money ELSE 0 END) As money_on_january,
SUM(CASE month WHEN 'february' THEN money ELSE 0 END) As money_on_february,
...
FROM
YourTable
GROUP BY
user
If you only want columns for the months which exist in the table, then you'll need to use dynamic SQL instead.
CodePudding user response：
If you are using MS SQL, Try PIVOT
SELECT * FROM [Your Table]
PIVOT(
SUM([money])
FOR [month] IN ([january],[april],[march])
)pvt