2

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

2

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.

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

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.