根據不同的條件從不同的行中選擇相同的列

我的表為唯一客戶編號的每個客戶名稱創建一行。

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);

我需要能夠根據“Seq”編號返回具有多個名稱列的單行。它總是要麼是 0 要麼是 1，並且 Seq 1 有時可以是空白的。

鑑於此表和數據：

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);

您可以應用一個簡單的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;

結果：

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

我只會確保它Number, seq被強制為唯一，並且它seq有點或有一個約束，因此它只能是0or 1。

另一種不使用樞軸的方法

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; 

結果

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

---

更新：許多列的問題

如果註釋中提到的其他列基於數字相同，您可以將它們添加到 group by，並將TEXT值轉換為( varchar(4000) max 3640 datalength)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));

免責聲明：性能可能不是最佳的

引用自：https://dba.stackexchange.com/questions/234964