SQL Server，将两条记录合并到一条记录中

Question

我们有这些表

CREATE TABLE tbl01
(
    [id] int NOT NULL PRIMARY KEY,
    [name] nvarchar(50) NOT NULL
)

CREATE TABLE tbl02
(
    [subId] int NOT NULL PRIMARY KEY ,
    [id] int NOT NULL REFERENCES tbl01(id),
    [val] nvarchar(50) NULL,
    [code] int NULL
)

如果我们运行这个查询:

SELECT  
   tbl01.id, tbl01.name, tbl02.val, tbl02.code
FROM    
   tbl01 
INNER JOIN 
   tbl02 ON tbl01.id = tbl02.id

我们得到这些结果:

-------------------------------
id  | name  | val       | code
-------------------------------
1   | one   | FirstVal  | 1
1   | one   | SecondVal | 2
2   | two   | YourVal   | 1
2   | two   | OurVal    | 2
3   | three | NotVal    | 1
3   | three | ThisVal   | 2
-------------------------------

你可以看到每两行都与同一个“id”相关

问题是:我们需要为每个id检索一条包含所有val的记录，每个val将根据code

列的值

if(code = 1) then val as val-1
else if (code = 2) then val as val-2

像这样:

-------------------------------
id  | name  | val-1     | val-2
-------------------------------
1   | one   | FirstVal  | SecondVal
2   | two   | YourVal   | OurVal
3   | three | NotVal    | ThisVal
-------------------------------

有什么建议吗？

Answer 1

使用可以使用MAX和Group By来实现

SELECT  id,
        name,
        MAX([val1]) [val-1],
        MAX([val2]) [val-2]
FROM    ( SELECT    tbl01.id, tbl01.name,
                    CASE code
                      WHEN 1 THEN tbl02.val
                      ELSE ''
                    END [val1],
                    CASE code
                      WHEN 2 THEN tbl02.val
                      ELSE ''
                    END [val2]
          FROM      tbl01
                    INNER JOIN tbl02 ON tbl01.id = tbl02.id
        ) Tbl
GROUP BY id, name