# Selecting the same column from Different rows Based on Different Criteria

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

• OK thank you! So there's pretty much no way to run a simle query without creating a table first? – Nicole Montez Apr 16 at 19:58
• @NicoleMontez No problem, I am not sure what you mean by the 'without creating a table first'? I could have created a temp table aswell. As far as questions go, giving the sample data + table create statement really helps! We always have to create a table with the same sample data to show a valid resultset as an answer :). – Randi Vertongen Apr 16 at 20:03
• Error on Pivot table: Pivot grouping columns must be comparable. The type of column "JobMemo" is "text", which is not comparable. CREATE TABLE [dbo].[Debtors]( [Number] [int] NOT NULL, [Seq] [int] NULL, [Name] [varchar](30) NULL, [Street1] [varchar](30) NULL, [Street2] [varchar](30) NULL, [City] [varchar](30) NULL, [State] [varchar](3) NULL, [Zipcode] [varchar](10) NULL, [HomePhone] [varchar](30) NULL, – Nicole Montez Apr 16 at 20:07
• @NicoleMontez does the JobMemo need to be text? What does `SELECT MAX(DATALENGTH(JobMemo)) from dbo.Debtors` return? Maybe you could change it to varchar or nvarchar datatype? You could also cast it in the query. Could you add the entire table to the question? – Randi Vertongen Apr 16 at 20:27
• It's HUGE. I couldn't fit all of the characters. I'm sorry, this is my first question. I normally just wing it on my own. I inherited this table, and it's very invloved. – Nicole Montez Apr 16 at 20:32