Transform rows in columns

问题:

I have this table:

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

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

Here is the expected result:

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

NEW EDIT:

 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.

回答1:

For 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 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 Demo^{SQL Server}

---

Update:

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

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

---

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

回答2:

Just to expand on @Mahmoud Gamal's answer on the dynamic SQL version for SQL server. If you want to replace the null values with zeros, then you can use the following:

DECLARE @colsPivot AS NVARCHAR(MAX),
    @colsNull AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX);

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

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

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

exec(@query);

See SQL Fiddle with Demo

The result is:

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