-1

I have a set of data like below image.

enter image description here

How to turn this data into column like below

|--------------------------------------------------------------------------------------------------------------------------------------|
| pcamhd_projectid | pcamhd_year |    1    |    2   |    3   |   4   |   5   |   6  |   7   |   8   |   9   |   10   |   11   |   12   |
| 987              | 2018        | 1782.52 | 214.66 |  62.52 | 0.00  | 0.00  | 0.00 | 0.00  | 20.00 | 0.00  | 0.00   | 0.00   | 0.00   |
softboxkid
  • 877
  • 6
  • 13
  • 31
  • 1
    Please don't add tables as image, i could help, but i really don't want to type your table. – nbk Aug 25 '19 at 13:49
  • 1
    This question is a duplication for sure...A Stackoverflow search ["MySQL pivot"](https://stackoverflow.com/search?q=MySQL+pivot) or ["MySQL convert records into columns"](https://stackoverflow.com/search?q=MySQL+convert+records+into+columns) should provide more then enough information/examples to solve.. – Raymond Nijland Aug 25 '19 at 13:52
  • 1
    Possible duplicate of [MySQL pivot table query with dynamic columns](https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns) – Raymond Nijland Aug 25 '19 at 13:54

1 Answers1

0

you should use a pivot query, i will demonstrate it:

 SELECT pcamhd_projectid,
sum(pcamhd_debtrepayment*(1-abs(sign(pcamhd_period-1)))) as '1',
sum(pcamhd_debtrepayment*(1-abs(sign(pcamhd_period-2)))) as '2',
sum(pcamhd_debtrepayment*(1-abs(sign(pcamhd_period-3)))) as '3',
sum(pcamhd_debtrepayment*(1-abs(sign(pcamhd_period-4)))) as '4',
sum(pcamhd_debtrepayment*(1-abs(sign(pcamhd_period-5)))) as '5',
sum(pcamhd_debtrepayment*(1-abs(sign(pcamhd_period-6)))) as '6',
sum(pcamhd_debtrepayment*(1-abs(sign(pcamhd_period-7)))) as '7',
sum(pcamhd_debtrepayment*(1-abs(sign(pcamhd_period-8)))) as '8',
sum(pcamhd_debtrepayment*(1-abs(sign(pcamhd_period-9)))) as '9',
sum(pcamhd_debtrepayment*(1-abs(sign(pcamhd_period-10)))) as '10',
sum(pcamhd_debtrepayment*(1-abs(sign(pcamhd_period-11)))) as '11',
sum(pcamhd_debtrepayment*(1-abs(sign(pcamhd_period-12)))) as '12'
FROM test.pc_amortizeheader
group by pcamhd_projectid;
  • sing (x) returns -1,0,+1 for values x < 0, x = 0, x >0 respectively
  • abs( sign(x) ) returns 0 if x = 0 else, 1 if x > 0 or x < 0
  • 1 - abs( sign(x) ) complement of the above, since this returns 1 only if x = 0
Klienblat Moshe
  • 322
  • 1
  • 6
  • Yes, this is the solution. Many example from the internet always group by ID. But my question is not suitable to group by ID. It suppose to group by projectid like your solution. thanks! – softboxkid Aug 25 '19 at 23:22