4

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?

0
5

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
0
3

With only 2 value per Name:

WITH d AS (
    SELECT Name, [value]
        , ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Sequence) n
    FROM @data
)
SELECT d1.Name, d1.Value As [Value+], d2.Value As [Value-]
FROM d d1 
INNER JOIN d d2 ON d1.Name = d2.Name AND d1.n = 1 AND d2.n = 2
;

This should work with any number of value for each name:

WITH d AS (
    SELECT Name, [value]
        , ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Sequence) mn
        , ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Sequence DESC) mx
    FROM data
)
SELECT d1.Name, d1.Value As [Value+], d2.Value As [Value-]
FROM d d1 
INNER JOIN d d2 ON d1.Name = d2.Name AND d1.mn = 1 AND d2.mx = 1
;

Sample data:

declare @data table
    (Name varchar(5), Value varchar(3), Sequence int)
;

INSERT INTO @data
    (Name, [value], Sequence)
VALUES
    ('test', 'A1', 1),
    ('test', 'A2', 3),
    ('test2', 'A20', 5),
    ('test2', 'A10', 8)
;
3

Unless you are using SQL Server 2000 or earlier version, you can also do this with PIVOT:

WITH ranked AS
  (
    SELECT
      Name,
      Value,
      RN = ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Sequence)
    FROM
      dbo.atable
  )
SELECT
  Name,
  [Value+] = [1],
  [Value-] = [2]
FROM
  ranked
  PIVOT (MIN(Value) FOR RN IN ([1], [2])) AS p
;

This is essentially equivalent to @spaghettidba's solution, just uses different syntax.

Here is how the query works. The ranked CTE evaluates to this row set:

Name   Value  RN
-----  -----  --
test   A1     1
test   A2     2
test2  A20    1
test2  A10    2

As you can see it gives the ranking of 1 to each row with the lower Sequence per Name, and ranking of 2 to that with the higher Sequence.

The main query's PIVOT clause turns the above into this row set:

Name   1    2
-----  ---  ---
test   A1   A2
test2  A20  A10

As the result of the pivoting, Values are divided into columns whose names are derived from the RN column.

And the SELECT clause of the main query simply renames columns 1 and 2 to Values+ and Values-, respectively, to give you the desired output.

2
  • Why do you need MIN here? Or rather, what is it doing in the pivot statement? – fraxture Jul 3 '19 at 20:48
  • @fraxture: The PIVOT syntax requires that you use an aggregate function on the pivoted column. In this case there's no need to aggregate anything really, so we just need to choose an aggregate function that will return the column value unchanged. Both MIN and MAX work equally well for that. I chose MIN. By the way, it's the same with spaghettidba's pivoting technique. It uses MAX, but you could replace it with MIN and the results would be the same. – Andriy M Jul 4 '19 at 7:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.