SQL - 如何根据唯一值组合行

2021-09-14 00:00:00 string sql select unique sql-server

下表是从另一个包含 ID、Name、Organ 和 Age 列的表中创建的.器官列中的值是指定器官和状况的代码.

The table below was created from another table with columns ID,Name,Organ,and Age. The values found in the Organ column were codes which designated both organ and condition.

使用 CASE 我做了一个这样的表格:

Using CASE I made a table like this:

--------------------------------------------------------
ID      NAME        Heart   Brain   Lungs   Kidneys AGE
1318    Joe Smith   NULL    NULL    NULL    NULL    50
1318    Joe Smith   NULL    NULL    NULL    NULL    50
1318    Joe Smith   NULL    NULL    NULL    Below   50
1318    Joe Smith   NULL    NULL    NULL    Below   50
1318    Joe Smith   NULL    NULL    Above   NULL    50
1318    Joe Smith   NULL    NULL    Above   NULL    50
1318    Joe Smith   Average NULL    NULL    NULL    50
1318    Joe Smith   Average NULL    NULL    NULL    50
--------------------------------------------------------

我想查询这个表并得到以下结果:

I would like to query this table and get the following result:

--------------------------------------------------------
1318    Joe Smith   Average NULL    Above   Below   50   
--------------------------------------------------------

换句话说，我想根据每个记录的唯一值创建一个记录列.

In other words I would like to create one record based on the unique values from each column.

推荐答案

假设每个器官可以只有一个值或 null，如示例数据所示，max 聚合函数应该可以解决问题:

Assuming each organ can either have just one value or a null, as shown in the sample data, the max aggregate function should do the trick:

SELECT   id, name, 
         MAX(heart), MAX(brain), MAX(lungs), MAX(kidneys), 
         age
FROM     my_table
GORUP BY id, name, age

相关文章