CODEWITHSUNDEEP
mysqlsql
Lijibals
Lijibals

Reputation: 17

Row as column in MySQL

I have mysql db, where i have data of some company (row wise). I need a report which should be company daily transaction where the company names should display as column and the amount in the same table as rows.

The report is generating, but with null values.

Table Structure:

------------------------------------------
company_id |   date    | amount | party_id
------------------------------------------
1000       | 1-1-14    | 10000  | 101
1001       | 2-1-14    | 20900  | 102
1001       | 2-1-14    | 20000  | 102
1000       | 1-1-14    | 10000  | 101
1000       | 2-1-14    | 21000  | 101
------------------------------------------

I want report in this format:

---------------------
1000  |  1001 | 1002 
---------------------
10000 | 20900 | NULL
10000 | 20000 | NULL
21000 | NULL  | NULL
---------------------

Should return 3 rows.

I am using this below mentioned query:

SELECT 
       (CASE WHEN company_id = '1000' THEN transaction_amount  END) `1000`,
       (CASE WHEN company_id = '1001' THEN transaction_amount END) `1001`,
       (CASE WHEN company_id = '1003' THEN transaction_amount END) `1002`,
FROM table3;

But i am getting many rows with NULL.

---------------------
1000  |  1001 | 1002 
---------------------
10000 | NULL  | NULL
NULL  | 20900 | NULL
NULL  | 20000 | NULL
10000 | NULL  | NULL
21000 | NULL  | NULL
---------------------

query is returning 5 rows.

I tried order by, group by but without success. Please help me.

Upvotes: 1

Views: 64

Answers (1)

peterm
peterm

Reputation: 92785

In your original query you didn't use GROUP BY that's why you get in your resultset as many rows as you have in your table.

Now the problem with the GROUP BY was caused by the fact that you have no column for grouping your rows. Therefore to produce the desired outcome you can introduce this non-exitent column - a row number per each company_id ordered by date

SELECT MAX(CASE WHEN company_id = 1000 THEN amount END) `1000`,
       MAX(CASE WHEN company_id = 1001 THEN amount END) `1001`,
       MAX(CASE WHEN company_id = 1002 THEN amount END) `1002`
  FROM
(
  SELECT t.*,
         @n := IF(@c = company_id, @n + 1, 1) rnum, @c := company_id
    FROM table1 t CROSS JOIN (SELECT @n := 0, @c := NULL) i
   ORDER BY company_id, date
) q
 GROUP BY rnum

Output:

|  1000 |   1001 |   1002 |
|-------|--------|--------|
| 10000 |  20900 | (null) |
| 10000 |  20000 | (null) |
| 21000 | (null) | (null) |

Here is SQLFiddle demo

Upvotes: 1

Related Questions