SQL Server - 为此目的的正确查询是什么？

I have a TABLE that contains, for example:

我有一个包含的TABLE,例如:

ID        CVG
4         A
4         C
5         B

Each row contains a coverage for a given ID. If the ID does not have a coverage, there will be no row in the table (ex. ID 4 has no coverage B).

每行包含给定ID的coverage。如果ID没有覆盖范围,则表中不会有任何行(例如,ID 4没有覆盖范围B)。

From this table I want to write a query like

从这个表我想写一个像这样的查询

SELECT ID, A = case (when ??? then 'TRUE'  when ??? then 'FALSE'), B = case (...), C = case (...) FROM TABLE

that will give the output

这将给出输出

ID       A       B        C
4        TRUE    FALSE    TRUE
5        FALSE   TRUE     FALSE

where there is only one row for each ID, and one column for each coverage.

每个ID只有一行,每个coverage只有一列。

What's a workable query for this? I'm not allowed to change any permanent tables on the server so I'm trying to reach a solution with what I have.

什么是可行的查询?我不允许更改服务器上的任何永久表,所以我试图用我所拥有的解决方案。

3 个解决方案

#1

You could do something like this:

你可以这样做:

select
  ID,
  max(case when CVG = 'A' then 'TRUE' else 'FALSE' end),
  max(case when CVG = 'B' then 'TRUE' else 'FALSE' end),
  max(case when CVG = 'C' then 'TRUE' else 'FALSE' end),
from
  table
group by
  ID

The case + max will select the true if even one row is found for that ID, otherwise false.

如果为该ID找到一行,则大小写+ max将选择true,否则为false。

#2

You can use conditional aggregation to get the required result:

您可以使用条件聚合来获取所需的结果:

SELECT ID, 
       CASE 
          WHEN COUNT(CASE WHEN CVG = 'A' THEN 1 END)>=1 THEN 'TRUE'
          ELSE 'FALSE'
       END AS A,
       CASE 
          WHEN COUNT(CASE WHEN CVG = 'B' THEN 1 END)>=1 THEN 'TRUE'
          ELSE 'FALSE'
       END AS B,
       CASE 
          WHEN COUNT(CASE WHEN CVG = 'C' THEN 1 END)>=1 THEN 'TRUE'
          ELSE 'FALSE'
       END AS C,
       .... etc
FROM mytable
GROUP BY ID

SQL Fiddle Demo

SQL小提琴演示

#3

If you want to have it select where

如果你想让它选择在哪里

A = case (when ??? then 'TRUE'  when ??? then 'FALSE'), B = case (...), C = case (...)

which is what I got from your explenation, what you need to do is

这是我从你的言论中得到的,你需要做的是

SELECT id, A, B, C FROM TABLE WHERE A = case (when ??? then 'TRUE'  when ??? then 'FALSE'), B = case (...), C = case (...)

#1