MYSQL - 展平表查询 [英] MYSQL - Flatten Table Query

问题描述

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

I am having some issues with the below query, I was unsure as to how to modify it to give me the desired output which is detailed further 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;

输出如下:

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 was wanting to have it all appear on one line, so flattened as such.. eg.

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

推荐答案

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

You need to define a GROUP BY clause, and use the MAX aggregate to get the output you want:

  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 比零长度字符串更可取.

NULL is preferable to a zero length string.

这篇关于MYSQL - 展平表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！