转换列中的行 [英] Transform rows in columns

问题描述

我有这个表：

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

如何在多个栏中建立栏值 name 。

How can I make the column values ​​name in multiple columns.

这是预期的结果：

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

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

I don't know how many columns there will be (it's dynamic).

新编辑：

 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    |

现在，唯一的区别是，不是有动态列，只有静态列，

Now, the only difference is that instead of having dynamic columns, there are only static columns, however the first row of the table is such dynamic values ​​ranging look at another table.

推荐答案

对于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 Fiddle演示

对于SQL Server：

SQL Fiddle Demo

For 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}

---

更新：

如果这些值好，坏，非常好来自另一个表，动态。

SQL Fiddle Demo^{SQL Server}

---

Update:

If these values good, bad, very good are coming form another table and you want to do this dynamically.

对于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，动态}

SQL Fiddle Demo^{MySQL, dynamic}

|   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， p>

---

For SQL Server you can do this:

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，动态}

SQL Fiddle Demo^{SQL Server, dynamic}

This will give you:

|   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) |

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