将2行转换为SQL Server中的字段

In SQL server (2016), I want to convert 2 rows into 1 row with fields of both rows. I have this example:

在SQL server（2016）中，我想将2行转换为包含两行字段的1行。我有这个例子：

IF OBJECT_ID('tempdb.dbo.#MyTable') IS not NULL DROP TABLE #MyTable

CREATE TABLE #MyTable (
    Direction varchar(1),
    DateKey int,
    ID varchar(8),
    [Sessions] int
    )

insert into #MyTable values('S', 20180301, 'ID123456', 46)
insert into #MyTable values('R', 20180301, 'ID123456', 99)

select * from #MyTable

Output:

输出：

Direction   DateKey     ID          Sessions
S           20180301    ID123456    46
R           20180301    ID123456    99

The output I want is:

我想要的输出是：

DateKey     ID          S_Sessions  R_Sessions
20180301    ID123456    46          99

So I tried this query but it won't work:

所以我尝试了这个查询，但它不起作用：

select DateKey,ID,
    case Direction
    when 'S' then [Sessions] as S_Sessions -- Incorrect syntax near the keyword 'as'.

    else [Sessions] as R_Sessions
    end
from #MyTable

Maybe I have to create an extra table, insert rows where direction='S' and then update the records with data where direction='R' but I wonder if there is a better way to do this.

也许我必须创建一个额外的表，插入direction ='S'的行，然后用direction ='R'的数据更新记录，但我想知道是否有更好的方法来做到这一点。

4 个解决方案

#1

CASE in SQL is an expression that returns a single value. It cannot be used to control execution flow like in procedural languages.

SQL中的CASE是一个返回单个值的表达式。它不能像过程语言那样用于控制执行流程。

You can use conditional aggregation for this:

您可以使用条件聚合：

select DateKey, ID,
    max(case Direction when 'S' then [Sessions] end) as S_Sessions,
    max(case Direction when 'R' then [Sessions] end) as R_Sessions       
from #MyTable
group by DateKey, ID

Demo here

在这里演示

#2

use PIVOT

使用PIVOT

select  *
from    #MyTable
        pivot
        (
            max(Sessions)
            for Direction in ([S], [R])
        ) p

#3

assuming that your table contains the "pairs" S and R you can also use a self join

假设您的表包含“对”S和R，您还可以使用自联接

SELECT s.DateKey , s.ID , s.Sessions S_Sessions , r.Sessions R_Sessions
FROM #MyTable S
JOIN #MyTable R
ON s.ID = r.ID
AND s.DateKey = r.DateKey
WHERE S.Direction = 'S'
AND r.Direction = 'R'

#4

Try It ... It works for me . more variable more case and more left join table.

试试吧......它适合我。更多变量更多案例和更多左连接表。

select a.DateKey,a.ID,
    (case a.Direction
    when 'S' then a.Sessions 
    end) as S_Sessions,
    (case b.Direction
    when 'R' then b.Sessions 
    end) as R_Sessions
from mytable as a CROSS JOIN mytable as b ON a.ID=b.ID LIMIT 2,1

#1