SQL Convert rows into columns-CodePudding

I have the following output:

Username	shortname	data
test1	firm	Google
test1	type	IT
test1	agreed	1
test2	firm	Facebook
test2	type	IT
test2	agreed	1
test2	newsletter	1

I want to make another column that makes the following: if username has the shortname "newsletter" and "newsletter" has the data "1" then "yes", if username does not have newsletter then "no"

So I want to have the following output:

Username	yes/no
test1	no
test2	yes

I made the following code:

SELECT distinct(if(shortname = 'newsletter' and data = 1, 'Yes', 'No')) as 'Yes/no', username
FROM prefix_user as u
JOIN prefix_user_info_data as uid ON uid.userid = u.id
JOIN prefix_user_info_field as uif ON uid.fieldid = uif.id

But this writes out yes or no in every single row

CodePudding user response：

You can use conditional aggregation as

SELECT username,
       MAX(CASE
           WHEN shortname = 'newsletter' AND data = 1 THEN
            'Yes'
           ELSE
            'No'
           END) AS "yes/no"
  FROM prefix_user as u
  JOIN prefix_user_info_data as uid
    ON uid.userid = u.id
  JOIN prefix_user_info_field as uif
    ON uid.fieldid = uif.id
 GROUP BY username

considering the alphabetical order of the word Yes comes later than No

CodePudding user response：

Is this on Oracle? Actually even if not you may take the idea on below query.

select case 
        when shortname = 'newsletter' and data = '1'
         then 'YES' 
        else 'NO' 
       end as yes_no 
  from prefix_user as u 
  JOIN prefix_user_info_data as uid 
    ON uid.userid = u.id 
  JOIN prefix_user_info_field as uif 
    ON uid.fieldid = uif.id

CodePudding user response：

From the tags you've added, I'm taking it as MySQL. In your IF condition, in the case where shortname='newsletter', you will have to add a nested IF condition to check if the data value is equal to 1.

The query will be something like following for checking for all the users will be selected instead of DISTINCT based on the IF conditon:

SELECT
    IF( shortname = 'newsletter',
        IF (
            data= 1,
            'yes',
        'No')
    , 'No') AS 'Yes/no',
    username,shortname, data
FROM
    test1 AS u   //Relace your table name here
//add joins with other tables that you requires.