Convert Two Rows into Two Columns in SQL Server

pivotsql server

I have this table:

Name        Value      Sequence    
-------------------------------    
test        A1         1           
test        A2         3           
test2       A20        5         
test2       A10        8

I am after grouping the Name and get Value+ and Value- like this:

Name        Value+         Value-    
-----------------------------------    
test        A1             A2          
test2       A20            A10

Rules are:

  • The Value from row with lower Sequence goes to Value+
  • The Value from row with higher Sequence goes to Value-
  • There are only two rows per name

How can I do it?

Best Answer

Here's a solution with a crosstab:

DECLARE @SampleData TABLE (
    Name varchar(50),
    Value varchar(50),
    Sequence int
)

INSERT INTO @SampleData
VALUES
 ('test'        ,'A1',         1)       
,('test'        ,'A2',         3)       
,('test2'       ,'A20',        5)       
,('test2'       ,'A10',        8);

WITH RankedData AS (
    SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Sequence)
    FROM @SampleData
)
SELECT Name, 
    [Value+] = MAX(CASE WHEN RN = 1 THEN Value ELSE '' END),
    [Value-] = MAX(CASE WHEN RN = 2 THEN Value ELSE '' END)
FROM RankedData
GROUP BY Name
Related Question