如何从一个表返回多个列？

How do i return multiple columns from a table ?

如何从表中返回多个列？

My Course table:

我的课程表：

| Course | PersId | Taskid | Status |

|课程| PersId | Taskid |状态|

Computer < > User1 <> 1 < > confirmed
Computer < >User2 <> 1 <> unconfirmed
Computer < >User3 <> 1 <> unconfirmed
Computer < >User1 <> 2 <> confirmed
Computer < >User2 <> 2 <> confirmed
Computer < >User3 <> 2 <> unconfirmed

计算机<> User1 <> 1 <>已确认计算机<>用户2 <> 1 <>未确认计算机<>用户3 <> 1 <>未确认计算机<>用户1 <> 2 <>已确认计算机<>用户2 <> 2 <>已确认计算机<> User3 <> 2 <>未经证实

I want it to return like this:

我希望它像这样返回：

| PersId | Task_1 | Task_2 |

| PersId | Task_1 | Task_2 |

User1 <> confirmed < > confirmed
User2 <> unconfirmed <> confirmed
User3 <> unconfirmed <> unconfirmed

User1 <>已确认<>已确认User2 <>未确认<>已确认User3 <>未确认<>未确认

Question 2:
There are other courses(math, english etc) in my table with more tasks than two. Do i need to use some kind of iteration to return the task columns ? because i dont want to make a SQL query for each single course(over 100).

问题2：我的表中还有其他课程（数学，英语等），任务多于两个。我是否需要使用某种迭代来返回任务列？因为我不想为每个单一课程（超过100）制作SQL查询。

thanks in advance

提前致谢

3 个解决方案

#1

You can use a CASE and an aggregate:

您可以使用CASE和聚合：

select persid,
  max(case when taskid = 1 then status end) as Task1,
  max(case when taskid = 2 then status end) as Task2
from course
group by persid

If you want to include the course info:

如果您想要包含课程信息：

select persid,
  course,
  max(case when taskid = '1' then status end) as Task1,
  max(case when taskid = '2' then status end) as Task2
from course
group by persid, course
order by course, persid

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

If you have an unknown number of tasks, then you can use a prepared statement to generate this dynamically:

如果您具有未知数量的任务，则可以使用预准备语句动态生成此任务：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when taskid = ''',
      taskid,
      ''' then status end) AS Task_',
      taskid
    )
  ) INTO @sql
FROM course;

SET @sql = CONCAT('SELECT persid, course, ', @sql, ' 
                  FROM course 
                  group by persid, course
                  order by course, persid');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

#2

Like this:

喜欢这个：

SELECT 
  PersId,
  MAX(CASE WHEN Taskid = 1 THEN Status END) AS Task_1,
  MAX(CASE WHEN Taskid = 2 THEN Status END) AS Task_2
FROM Courses
GROUP BY persID

#3

Use power of PIVOTE man

使用PIVOTE男人的力量

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

PIVOT通过将表达式中一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在最终输出中需要的任何剩余列值上执行聚合。

SELECT Course, PersId, [1] AS [Task_1], [2] AS [Task_2]
FROM
 (
  SELECT Course, PersId, TaskId, [Status]
  FROM t2
  ) as a
PIVOT
 (
  MAX([Status])
  FOR TaskId IN ([1], [2])
  ) as b