MYSQL - smooth table query

Question

I'm having some problems with the query below, I'm not sure how to change it to give me the desired result, which is described in detail below:

SELECT listup.NodeNumber As Node, listup.Station As Extension, CASE WHEN VoiceServer = 1 THEN KeyDescription ELSE '' END AS 'Key1', CASE WHEN VoiceServer = 2 THEN KeyDescription ELSE '' END AS 'Key2', CASE WHEN VoiceServer = 3 THEN KeyDescription ELSE '' END AS 'Key3', CASE WHEN VoiceServer = 4 THEN KeyDescription ELSE '' END AS 'Key4', CASE WHEN VoiceServer = 5 THEN KeyDescription ELSE '' END AS 'Key5', CASE WHEN VoiceServer = 6 THEN KeyDescription ELSE '' END AS 'Key6', CASE WHEN VoiceServer = 7 THEN KeyDescription ELSE '' END AS 'Key7', CASE WHEN VoiceServer = 8 THEN KeyDescription ELSE '' END AS 'Key8', CASE WHEN VoiceServer = 9 THEN KeyDescription ELSE '' END AS 'Key9', CASE WHEN VoiceServer = 10 THEN KeyDescription ELSE '' END AS 'Key10' FROM listup ORDER BY listup.NodeNumber, listup.Station; 

The output is as follows:

 Node Extension Key1 Key2 Key3 etc. N100 14311 14311 N100 14311 14308 N100 14311 14309 N100 14311 14314 N100 14311 14412 N100 14311 14535 N100 14311 14316 N100 14311 14456 N100 14312 14312 N100 14312 14442 N100 14312 14311 N100 14312 14314 N100 14312 14456 N100 14312 14309 N100 14312 14308 

I wanted all this to appear on the same line, so it was flattened as such. eg.

 N100 14311 14311 14308 14309 14314 14412 14535 14316 14456 N100 14312 14312 14442 14311 14314 14456 14309 14308 

Answer 1

You need to define a GROUP BY and use the MAX aggregate to get the desired result:

  SELECT listup.NodeNumber As Node, listup.Station As Extension, MAX(CASE WHEN VoiceServer = 1 THEN KeyDescription ELSE NULL END) AS 'Key1', MAX(CASE WHEN VoiceServer = 2 THEN KeyDescription ELSE NULL END) AS 'Key2', MAX(CASE WHEN VoiceServer = 3 THEN KeyDescription ELSE NULL END) AS 'Key3', MAX(CASE WHEN VoiceServer = 4 THEN KeyDescription ELSE NULL END) AS 'Key4', MAX(CASE WHEN VoiceServer = 5 THEN KeyDescription ELSE NULL END) AS 'Key5', MAX(CASE WHEN VoiceServer = 6 THEN KeyDescription ELSE NULL END) AS 'Key6', MAX(CASE WHEN VoiceServer = 7 THEN KeyDescription ELSE NULL END) AS 'Key7', MAX(CASE WHEN VoiceServer = 8 THEN KeyDescription ELSE NULL END) AS 'Key8', MAX(CASE WHEN VoiceServer = 9 THEN KeyDescription ELSE NULL END) AS 'Key9', MAX(CASE WHEN VoiceServer = 10 THEN KeyDescription ELSE NULL END) AS 'Key10' FROM listup GROUP BY listup.NodeNumber As Node, listup.Station As Extension ORDER BY listup.NodeNumber, listup.Station 

NULL preferred over zero-length strings.