mysql:在select result中，列值作为列名

i have a table which i group by one column and then i output the different values and the count of this values.

我有一个表，我按一列进行分组，然后输出不同的值和这些值的计数。

activty | sum
-------------
Form    |  1
Login   |  4
Reg     |  3

here is an example-code: http://sqlfiddle.com/#!2/c6faf/2/0

这里有一个示例代码:http://sqlfiddle.com/#!2/c6faf/2/0

but i want the different values tp the column names with one row of values (the count).

但是我想要不同的值，包含一行值(计数)的列名。

like this:

是这样的:

Form | Login | Reg
------------------
  1  |   4   |  3

i tried the PIVOT operation, but i'm not getting it working. what am i doing wrong?

我尝试了旋转操作，但是我没有让它工作。我做错了什么?

here is my code: http://sqlfiddle.com/#!2/c6faf/35/0

这是我的代码:http://sqlfiddle.com/#!2/c6faf/35/0

thanks in advance!

提前谢谢!

3 个解决方案

#1

Try this:

试试这个:

SELECT  
    SUM(IF(activity = 'Form',1,0)) as 'Form',
    SUM(IF(activity = 'Login',1,0)) as 'Login',
    SUM(IF(activity = 'Reg',1,0)) as 'Reg'
FROM 
    tbl_tracking
WHERE 
    id = 141 
    AND variant = 2

Sql Fiddle here

Sql小提琴在这里

#2

Here you go

在这里你去

SELECT 
MAX(CASE activity WHEN 'Form' THEN mysum ELSE NULL END) AS Form,
MAX(CASE activity WHEN 'Login' THEN mysum ELSE NULL END) AS Login,
MAX(CASE activity WHEN 'Reg' THEN mysum ELSE NULL END) AS Reg
FROM (
SELECT activity, COUNT(activity) AS mysum FROM tbl_tracking
            WHERE id = 141 AND variant = 2
            GROUP BY activity
)sq

But in my opinion such formatting should be done in application layer, not in database layer.

但是我认为这种格式应该在应用层而不是数据库层中进行。

#3

For this you need to use pivot table concept. here you go for same.

为此，您需要使用pivot表概念。这是一样的。

SELECT
  SUM((IF(activity='Form',1,0))) as Form,
  SUM((IF(activity='Login',1,0))) as Login,
  SUM((IF(activity='Reg',1,0))) as Reg
FROM 
  tbl_tracking
WHERE 
  id = 141 
  AND 
  variant = 2;

fiddle

小提琴

#1