How to combine several lines into one line?

Question

Possible duplicate:
Merge multiple child rows into a single MYSQL row

I have the following table,

count | inventory_id | resource_id | id ---------+-----------+---------------------- 10 | 1 | 1 | 1 20 | 2 | 1 | 2 30 | 1 | 2 | 3 40 | 2 | 2 | 4 

I want to combine lines where resource_id is equal, and also want to give an alias for each count according to inventory_id. I need to specify the alias "Resource 1" for count where inventory_id = 1 and "Resource 2", where inventory_id = 2

Is required

:

 Resource 1| Resource 2 |resource_id ---------+-----------+----------------- 10 | 20 | 1 30 | 40 | 2 

Answer 1

 SELECT MAX(CASE WHEN inventory_id = 1 THEN `count` END) AS 'Resource 1', MAX(CASE WHEN inventory_id = 2 THEN `count` END) AS 'Resource 2', resource_id FROM table GROUP BY resource_id 

SQL Fiddle DEMO

This should give you:

 RESOURCE 1 RESOURCE 2 RESOURCE_ID 10 20 1 30 40 2 

Answer 2

Try

 SELECT resource_id, MAX(CASE WHEN inventory_id = 1 THEN `count` ELSE NULL END) AS 'Resource 1', MAX(CASE WHEN inventory_id = 2 THEN `count` ELSE NULL END) AS 'Resource 2' FROM table1 GROUP BY resource_id 

SQLFiddle Demo

Strike>

but if you have an unknown inventory_id value is not easy ( 1 and 2 ), it is better to use PreparedStatement

 SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(CASE WHEN inventory_id = ', inventory_id, ' THEN `count` ELSE NULL END) AS ''RESOURCE ', inventory_id, '''' ) ) INTO @sql FROM table1; SET @sql = CONCAT('SELECT resource_id, ', @sql, ' FROM table1 GROUP BY resource_id'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; 

SQLFiddle Demo