MYSQL - 展平表查询

Question

我在使用以下查询时遇到了一些问题，我不确定如何修改它以提供我想要的输出，下面将对此进行详细说明:

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;

输出如下:

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            

我想让它全部出现在一条线上，如此扁平......例如。

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

Answer 1

您需要定义一个GROUP BY 子句，并使用MAX 聚合来获得您想要的输出:

  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 优于零长度字符串。