CODEWITHSUNDEEP
mysqlcodeigniteractiverecord
S. M. Shahinul Islam
S. M. Shahinul Islam

Reputation: 2800

How to convert rows into columns with codeigniter active record pattern?

Here is my table

+------+------+-------------+
| sid  | cid  | amount      |
+------+------+-------------+
| 32   | 1    | 500         |
+------+------+-------------+
| 33   | 1    | 300         |
+------+------+-------------+
| 32   | 2    | 500         |
+------+------+-------------+
| 33   | 2    | 400         |
+------+------+-------------+
| ..   | .    | ...         |
+------+------+-------------+
| ..   | .    | ...         |
+------+------+-------------+
| 32   | n    | 100         |
+------+------+-------------+
| 33   | n    | 100         |
+------+------+-------------+

In my view I want to show it in my view like

+------+----------+-----------+------+------------+------------+
| sid  | amount(1)| amount(2) |  ... | amount(n)  |Total amount|
+------+----------+-----------+------+------------+------------+
| 32   | 500      | 500       |  ... | 100        |1100        |
+------+----------+-----------+------+------------+------------+
| 33   | 300      | 400       |  ... | 100        |800         |
+------+----------+-----------+------+------------+------------+

How should I do it?

Upvotes: 0

Views: 1039

Answers (1)

kums
kums

Reputation: 2701

You can use GROUP_CONCAT, SUM and GROUP clause of SQL. However, there won't be multiple columns for amount. The list of amount for a sid will all be in one single column with values separated by comma.

SELECT sid,
       GROUP_CONCAT(amount) AS amount,
       SUM(amount) AS total
FROM mytbl
GROUP BY sid;

Upvotes: 2

Related Questions