Hfrxdjt

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.

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.

Another method without using pivot

CREATE TABLE dbo.Customer(Number int, [Name] varchar(255),Seq bit);
INSERT INTO dbo.Customer(Number,[Name],Seq)
VALUES
(12345,'Mickey Mouse',0),
(12345,'Minnie Mouse',1),
(45678,'Donald Duck',0),
(45678,'Daphney Duck',1),
(245678,'Pluto Dog',0);

SELECT Number, 
 MAX(CASE WHEN Seq = 0 THEN [Name] END) as Owner1, 
 MAX(CASE WHEN Seq = 1 Then [Name] END) AS Owner2
FROM dbo.Customer
GROUP BY Number; 

Result

Number Owner1 Owner2
12345 Mickey Mouse Minnie Mouse
45678 Donald Duck Daphney Duck
245678 Pluto Dog NULL

Update: Issue with many columns

If the other columns mentioned in the comments are the same based on the number, you can add them to the group by, and cast the TEXT value as varchar(4000) (max 3640 datalength) Otherwise you would have to choose one of the two with MAX / MIN / ...

SELECT Number, 
 MAX(CASE WHEN Seq = 0 THEN [Name] END) as Owner1, 
 MAX(CASE WHEN Seq = 1 Then [Name] END) AS Owner2
 ,[Street1], [Street2], [City] , [State] , [Zipcode] , [HomePhone] ,CAST(JobMemo as varchar(4000))
FROM dbo.Debtors
GROUP BY Number,[Street1], [Street2], [City] , [State] , [Zipcode] , [HomePhone] ,CAST(JobMemo as varchar(4000));

Disclaimer: the performance will probably not be optimal