CODEWITHSUNDEEP
sql-serverpivot
SBF
SBF

Reputation: 1369

Convert 2 rows to fields in SQL Server

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

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.

Upvotes: 0

Views: 36

Answers (4)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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

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

Upvotes: 0

StuckHeader
StuckHeader

Reputation: 26

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

Upvotes: 0

hkravitz
hkravitz

Reputation: 1385

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

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'

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

use PIVOT

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

Upvotes: 3

Related Questions