CODEWITHSUNDEEP
mysqlsqlselectunionwhere-clause
higeat
higeat

Reputation: 55

Selecting multiple rows from multiple columns as one column

As the title says I'm trying to select rows from Passive, Q, W, E, R that are not empty into one column https://i.sstatic.net/Ygj7q.png and here is what I've tried with no luck yet

SELECT (Passive + Q + W + E + R) AS spellchamps
FROM champions 
WHERE Patch_No='0.02' AND Champion = 'Katarina'
AND (
(Passive!='')
or(Q!='')
or(W!='')
or(E!='')
or(R!='')
)

It would be even better if I could incorporate it with my previous query that works fine that selects names of columns that are not empty 

SELECT column_name 
                        from information_schema.columns, patches.champions
                        where 
                        table_name = 'champions'
                        AND ((column_name ='Passive' and Passive!='')
                        or (column_name='Q' and Q!='')
                        or (column_name='W' and W!='')
                        or (column_name='E' and E!='')
                        or (column_name='R' and R!='')

                        )

                         AND Patch_No = '0.02'
                        AND Champion = 'Katarina'

EDIT this query gave me the right results but in 1 row and I want the results to be in separate rows

SELECT CONCAT(Passive, Q, W, E, R) AS spellchamps
FROM champions 
WHERE Patch_No='0.02' AND Champion = 'Katarina'
AND (
(Passive!='')
or(Q!='')
or(W!='')
or(E!='')
or(R!='')
)

Upvotes: 1

Views: 934

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT Passive AS spellchamps
FROM champions 
WHERE Patch_No='0.02' AND Champion = 'Katarina' AND 
      Passive IS NOT NULL AND Passive != ''
UNION 
SELECT Q AS spellchamps
FROM champions 
WHERE Patch_No='0.02' AND Champion = 'Katarina' AND 
      Q IS NOT NULL AND Q != ''
UNION 
SELECT W AS spellchamps
FROM champions 
WHERE Patch_No='0.02' AND Champion = 'Katarina' AND 
      W IS NOT NULL AND W != ''
UNION 
SELECT E AS spellchamps
FROM champions 
WHERE Patch_No='0.02' AND Champion = 'Katarina' AND 
      E IS NOT NULL AND E != ''
UNION 
SELECT R AS spellchamps
FROM champions 
WHERE Patch_No='0.02' AND Champion = 'Katarina' AND 
      R IS NOT NULL AND R != ''

Upvotes: 2

andrew
andrew

Reputation: 9583

You can use CONCAT

SELECT CONCAT(Passive, Q,  W, E,  R) AS spellchamps
FROM champions 
WHERE Patch_No='0.02' AND Champion = 'Katarina' 
AND '' NOT IN (Passive, Q, W, E, R)

Upvotes: 0

Related Questions