MySQL Dynamic Query Challenge - Help please!

I have this query:

   SELECT  
    userlist.USERID,  
    (case when (sum( CASE WHEN track.OFFER_ID = 221  THEN 1 ELSE 0 END) > 1) then 1 else 0 end) offer_211

FROM  
userlist  
INNER JOIN track ON userlist.USERID = track.USERID  

group by
userid

This is the output:

+------------+----------
| USERID     | offer_211 |
+------------+----------
| 1657487706 |         0 |  
| 1238开发者_开发百科439394 |         0 |  
| 1238427171 |         1 |  
| 1248431441 |         0 |  
| 1248464345 |         1 |  

Purpose of this query:

There is a user table, offer table and track table. Track table contains all the clicks from user table. I want to create a view like above, which will show me all the clicks from users for all the offers. If the user clicked on a certain offer it will display '1' under that offer column, if not it will display '0'. All the records are coming from track table.

Here is what i need help with: I want to be able to dynamically create columns "offer_211" from "offer" table instead of specifing them manually.(see my query)

So, for all the offers that a user clicked, or not clicked (columns) would be from offer the table.

it should be like this: "offer_"+offer.ID

It should look like this: (assuming there are only 2 offers in the offer table with ids 211, 212)

There could be 100's of offers in the offer table so there should be the same amount of dynamic columns for this view.

| USERID     | offer_211 | offer_212 |
+------------+----------+-------------
| 1657487706 |         0 |         1 |
| 1238439394 |         0 |         0 |
| 1238427171 |         1 |         0 |
| 1248431441 |         0 |         1 |
| 1248464345 |         1 |         0 |

track Table:

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| ID         | int(22)     | NO   | PRI | NULL    | auto_increment |
| OFFER_ID   | int(22)     | YES  | MUL | NULL    |                |
| USERID     | int(22)     | YES  | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

userlist Table:

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| USERID      | int(22)      | NO   | PRI | 0       |       |
| EMAIL       | varchar(200) | YES  |     | NULL    |       |
| FIRSTNAME   | varchar(100) | YES  |     | NULL    |       |
| LASTNAME    | varchar(100) | YES  |     | NULL    |       |

offer Table:

+------------------+--------------+------+-----+-------------+----------------+
| Field            | Type         | Null | Key | Default     | Extra          |
+------------------+--------------+------+-----+-------------+----------------+
| ID               | int(11)      | NO   | PRI | NULL        | auto_increment |
| NAME             | varchar(100) | YES  |     | NULL        |                |
| DESCRIPTION      | text         | YES  |     | NULL        |                |
| URL              | text         | YES  |     | NULL        |                |

This sort of query can become very messy, very quickly. I would suggest using more standard queries to obtain your data, then process that data in your application.

However, if there is a really good reason for creating a dynamic query, here's one way you can do it—these are just building blocks, so you'll need to modify it to suit your requirements:

Use CONCAT and GROUP_CONCAT to build a SELECT statement for use in a prepared statement:

SELECT CONCAT(
    'SELECT',
    GROUP_CONCAT(
        ' SUM(IF(offer_id = ', offer_id,
        ', 1, 0)) AS offer_', offer_id),
    ' FROM track;')
INTO @sql
FROM (SELECT DISTINCT offer_id FROM track) AS track;

Here's what the above command creates:

SELECT @sql;

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @sql                                                                                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT SUM(IF(offer_id = 1, 1, 0)) AS offer_1, SUM(IF(offer_id = 2, 1, 0)) AS offer_2, SUM(IF(offer_id = 3, 1, 0)) AS offer_3, SUM(IF(offer_id = 4, 1, 0)) AS offer_4 FROM track; |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Create and execute a prepared statement from the dynamic SQL:

PREPARE stmt FROM @sql;
EXECUTE stmt;

+---------+---------+---------+---------+
| offer_1 | offer_2 | offer_3 | offer_4 |
+---------+---------+---------+---------+
|       3 |       2 |       1 |       1 |
+---------+---------+---------+---------+

DEALLOCATE PREPARE stmt;

Have you considered getting a list of user IDs and offer IDs, sorted first by user ID and next by offer ID? It's the same information, just organized slightly differently, plus it'd be much, much faster for the database to execute a query like "select user_id, offer_id from track".

Mike,

Here what it returns: See at the end how its cut off - weird. What could it be?

I also didn't understand the last part of your comment - Last block;

Create and execute a prepared statement from the dynamic SQL:

PREPARE stmt FROM @sql;
EXECUTE stmt;

+---------+---------+---------+---------+
| offer_1 | offer_2 | offer_3 | offer_4 |
+---------+---------+---------+---------+
|       3 |       2 |       1 |       1 |
+---------+---------+---------+---------+

DEALLOCATE PREPARE stmt;

Here is the new query:

SELECT CONCAT(
    'SELECT',
    GROUP_CONCAT(
        ' SUM(IF(OFFER_ID = ', offer_id,
        ', 1, 0)) AS offer_', offer_id),
    ' FROM track;')
INTO @sql
FROM (SELECT DISTINCT ID as OFFER_ID from offer where `STATUS`=1) AS track;

select @sql;

Here is the output:

SELECT SUM(IF(OFFER_ID = 178, 1, 0)) AS offer_178, SUM(IF(OFFER_ID = 234, 1, 0)) AS offer_234, SUM(IF(OFFER_ID = 206, 1, 0)) AS offer_206, SUM(IF(OFFER_ID = 213, 1, 0)) AS offer_213, SUM(IF(OFFER_ID = 229, 1, 0)) AS offer_229, SUM(IF(OFFER_ID = 220, 1, 0)) AS offer_220, SUM(IF(OFFER_ID = 221, 1, 0)) AS offer_221, SUM(IF(OFFER_ID = 222, 1, 0)) AS offer_222, SUM(IF(OFFER_ID = 225, 1, 0)) AS offer_225, SUM(IF(OFFER_ID = 226, 1, 0)) AS offer_226, SUM(IF(OFFER_ID = 257, 1, 0)) AS offer_257, SUM(IF(OFFER_ID = 259, 1, 0)) AS offer_259, SUM(IF(OFFER_ID = 258, 1, 0)) AS offer_258, SUM(IF(OFFER_ID = 260, 1, 0)) AS offer_260, SUM(IF(OFFER_ID = 228, 1, 0)) AS offer_228, SUM(IF(OFFER_ID = 230, 1, 0)) AS offer_230, SUM(IF(OFFER_ID = 232, 1, 0)) AS offer_232, SUM(IF(OFFER_ID = 233, 1, 0)) AS offer_233, SUM(IF(OFFER_ID = 239, 1, 0)) AS offer_239, SUM(IF(OFFER_ID = 240, 1, 0)) AS offer_240, SUM(IF(OFFER_ID = 241, 1, 0)) AS offer_241, SUM(IF(OFFER_ID = 242, 1, 0)) AS offer_242, SUM(IF(OFFER_ID = 243, 1, 0)) AS offer_243, SUM(IF(OFFE FROM track;