Sql-server – Selecting the same column from Different rows Based on Different Criteria

pivotsql serversql-server-2012

My table creates a row for each customer name for the unique customer number.

CREATE TABLE #src(Number int, name varchar(32), seq bit);

INSERT #src(Number,name,seq) VALUES
(12345,'Mickey Mouse',0),
(12345,'Minnie Mouse',1),
(45678,'Donald Duck',0),
(45678,'Daphney Duck',1),
(245678,'Pluto Dog',0);

I need to be able to return a single row with multiple name columns based on the "Seq" number. It will always either be a 0 or a 1 and the Seq 1 can sometimes be blank.

enter image description here

Best Answer

Given this table and data:

CREATE TABLE #src(Number int, name varchar(32), seq bit);

INSERT #src(Number,name,seq) VALUES
(12345,'Mickey Mouse',0),
(12345,'Minnie Mouse',1),
(45678,'Donald Duck',0),
(45678,'Daphney Duck',1),
(245678,'Pluto Dog',0);

You can apply a simple PIVOT:

SELECT Number, Owner1 = [0], Owner2 = COALESCE([1],'')
  FROM #src AS c
  PIVOT (MAX(name) FOR seq IN ([0],[1])) AS p
  ORDER BY Number;

Results:

number   Owner1         Owner2
------   ------------   ------------
12345    Mickey Mouse   Minnie Mouse
45678    Donald Duck    Daphney Duck
245678   Pluto Dog

I would just ensure that Number, seq is enforced to be unique and that seq is either a bit or has a constraint so that it can only be 0 or 1.

Related Question