在列中提取表格行 [英] Fetch table rows in columns

问题描述

我想从我的表中得到一个特殊的结果，我想知道这是可能的！
我有一个这样的表：（idA，idB，val）这个值：

 
 idA | idB | val 
 ---- + ------- + ---------- 
 1 | 5 | 50 
 1 | 6 | 0 
 1 | 7 | NULL 
 2 | 5 | 100 
 2 | 6 | 12 
 2 | 7 | 0 
 
 
 
我想从这个表格中选择这样的格式：
 
 idA | 5 | 6 | 7 
 ---- + ------- + ------- + ------- 
 1 | 50 | 0 | NULL 
 2 | 100 | 12 | 0 
 
如何实现？
 
 
 
注意： 
解决方案
你可以尝试这个查询：
  SELECT idA 
，GROUP_CONCAT（CASE WHEN idB = 5 THEN val ELSE NULL END） GROUP_CONCAT（CASE WHEN idB = 6 THEN val ELSE NULL END）as`6` 
，GROUP_CONCAT（CASE WHEN idB = 7 THEN val ELSE NULL END）as`7` 
 From MyTable 
 GROUP BY idA 
  
如果您不知道 idB 您可以使用此动态查询：
  SET @sql = NULL; 
 SELECT 
 GROUP_CONCAT（DISTINCT 
 CONCAT（
'GROUP_CONCAT（CASE WHEN'idB` ='''，
`idB`，
' THEN val ELSE NULL END）as``，
`idB`，'`'
）
）INTO @sql 
 From MyTable; 
 
 SET @sql = CONCAT（'SELECT idA，'，@sql，'
 FROM MyTable 
 GROUP BY idA 
'）; 
 
 PREPARE stmt FROM @sql; 
 EXECUTE stmt; 
 DEALLOCATE PREPARE stmt; 
  
输出：
 code> | IDA | 5 | 6 | 7 | 
 --------------------------- 
 | 1 | 50 | 0 | （null）| 
 | 2 | 100 | 12 | 0 | 
  
 
 
 
请参阅 this SQLFiddle  
 
I want get a special result from my table and i want find out that is possible !?
I have a table like this: (idA , idB , val) by this values:
idA |   idB |   val
----+-------+----------
1   |   5   |   50
1   |   6   |   0
1   |   7   |   NULL
2   |   5   |   100
2   |   6   |   12
2   |   7   |   0
I want select from this table by a format like this:
idA |   5   |   6   |   7
----+-------+-------+-------
1   |   50  |   0   |   NULL
2   |   100 |   12  |   0
How it's possible?

Note: I have some searches and find nothing, if you know a good key word it would be useful for me.
解决方案
You can try this query:
SELECT idA
  ,GROUP_CONCAT(CASE WHEN idB = 5 THEN val ELSE NULL END) AS `5`
  ,GROUP_CONCAT(CASE WHEN idB = 6 THEN val ELSE NULL END) AS `6`
  ,GROUP_CONCAT(CASE WHEN idB = 7 THEN val ELSE NULL END) AS `7`
FROM MyTable
GROUP BY idA
If you don't know the number of idB you can use this dynamic query:
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT(CASE WHEN `idB` = ''',
      `idB`,
      ''' THEN val ELSE NULL END) AS `',
      `idB`, '`'
    )
  ) INTO @sql
FROM MyTable;

SET @sql = CONCAT('SELECT idA, ', @sql,'
                     FROM MyTable
                    GROUP BY idA
                  ');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Output:
| IDA |   5 |  6 |      7 |
---------------------------
|   1 |  50 |  0 | (null) |
|   2 | 100 | 12 |      0 |


See this SQLFiddle

                        
这篇关于在列中提取表格行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！