CODEWITHSUNDEEP
mysqlsql
softlyspoken
softlyspoken

Reputation: 173

Converting Rows To Columns - One Table

I have a table that has a few rows for one object. I'm trying to group the rows so that they form one column based off of a linking column. I have some SQL that will do this, but now I don't know how to make it conditional based off of another field.

******************************************************
* id * page_id *   key   *    value      *   uuid    *
******************************************************
* 1  *    1    *   type  *     url       * 654978654 *
******************************************************
* 2  *    1    * picture * 654978654.png * 654978654 *
******************************************************
* 3  *    1    *   url   *   google.com  * 654978654 *
******************************************************
* 4  *    1    *  active *      1        * 654978654 *
******************************************************

So this is basically just one entry in the table. I want to be able to have my select only work if the active for each uuid is set to 1. Below is the SQL that I am using to make my rows to columns (which does work).

Select uuid
    , Min( Case When meta_key = "type" Then meta_value End ) As Type
    , Min( Case When meta_key = "picture" Then meta_value End ) As Picture
    , Min( Case When meta_key = "url" Then meta_value End ) As URL
    , Min( Case When meta_key = "active" Then meta_value End ) As Active
From sm_page_meta
Where meta_key In("type","picture","url","active")
Group By uuid

Upvotes: 3

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

Just include a having clause:

Select uuid
    , Min( Case When meta_key = 'type' Then meta_value End ) As Type
    , Min( Case When meta_key = 'picture' Then meta_value End ) As Picture
    , Min( Case When meta_key = 'url' Then meta_value End ) As URL
    , Min( Case When meta_key = 'active' Then meta_value End ) As Active
From sm_page_meta
Where meta_key In('type', 'picture', 'url', 'active')
Group By uuid
Having Active = '1';

If you don't want to include Active in the result set, just put the whole expression in the HAVING clause.

Upvotes: 2

Related Questions