转换列中的行

小编典典

转换列中的行

sql

我有这张表：

idSection   | idQuestion    | title     | enunciation | total   | name 
1           | 1             | title 1   | question 1    | 5     | Good
1           | 1             | title 1   | question 1    | 3     | Very Good
1           | 1             | title 1   | question 1    | 1     | Bad
1           | 2             | title 2   | question 2    | 1     | Good
1           | 2             | title 2   | question 2    | 3     | Bad
1           | 3             | title 3   | question 3    | 1     | Bad

如何使多个列中的列值相同。

这是预期的结果：

title   | enunciantion  | bad   | good  | very good 
title 1 | question 1    | 1     | 5     | 3
title 2 | question 2    | 3     | 1     | 0
title 3 | question 3    | 1     | 0     | 0

我不知道会有多少列（它是动态的）。

新编辑：

 title      |  enunciantion | Column0  | Column1 | Column2 | ... | Column7 |
   0        |   0           | bad      |  good   |very good| ... |    0    |
title 1     | question 1    | 1        | 5       | 3       | ... |    0    |
title 2     | question 2    | 3        | 1       | 0       | ... |    0    |
title 3     | question 3    | 1        | 0       | 0       | ... |    5    |

现在，唯一的区别是，只具有静态列，而不是具有动态列，但是表的第一行是这种动态值-指向另一个表。

阅读 169

2021-05-16

共1个答案

小编典典

对于MySQL：

SELECT
  title,
  enunciantion,
  SUM(CASE WHEN name = 'bad' THEN total ELSE 0 END) AS 'bad',
  SUM(CASE WHEN name = 'good' THEN total ELSE 0 END) AS 'good',
  SUM(CASE WHEN name = 'very good ' THEN total ELSE 0 END) AS 'very good'
FROM Tablename
GROUP BY  title,
          enunciantion;

SQL小提琴演示

对于SQL Server：

SELECT
  title,
  enunciation,
  bad,
  good,
  [very good]
FROM Table1
PIVOT
(
  SUM(total) FOR name IN([good], [bad], [very good])
) p;

SQL Fiddle演示SQL Server

更新：

如果这些值good, bad, very good来自另一个表，并且您想动态地进行操作。

对于MySQL：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(Name = ''',
      Name, ''', Total, 0)) AS ',
      '''',Name, '''')
  ) INTO @sql
FROM names;


SET @sql = CONCAT('SELECT title, enunciation, ',
                  @sql,
                  'FROM Table1 GROUP BY  title, '
                  '  enunciation; ');
prepare stmt 
FROM @sql;

execute stmt;

SQL Fiddle演示MySQL，动态

|   TITLE | ENUNCIATION | GOOD | VERY GOOD | BAD | VERY BAD |
-------------------------------------------------------------
| title 1 |  question 1 |    5 |         3 |   1 |        0 |
| title 2 |  question 2 |    1 |         0 |   3 |        0 |
| title 3 |  question 3 |    0 |         0 |   1 |        0 |

对于SQL Server，您可以执行以下操作：

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.name) 
                    from names c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT  title, enunciation, ' + @cols +
              ' FROM (SELECT title, enunciation, total, name
      FROM Table1) t
PIVOT
(
  SUM(total) FOR name IN( ' + @cols + + ' )) p;';

execute(@query);

SQL Fiddle演示SQL Server，动态

这将为您提供：

|   TITLE | ENUNCIATION | BAD |   GOOD | VERY BAD | VERY GOOD |
---------------------------------------------------------------
| title 1 |  question 1 |   1 |      5 |   (null) |         3 |
| title 2 |  question 2 |   3 |      1 |   (null) |    (null) |
| title 3 |  question 3 |   1 | (null) |   (null) |    (null) |

2021-05-16