如何在SQL Server中使用筛选规则将多行合并为一行

Question

我有一个这样的表：

+---------------+---------------+----------------+---------------------+
| MedicalCardId | DiagnosisType | DiagnosisOrder |       Symptom       |
+---------------+---------------+----------------+---------------------+
|       1       |      Main     |       1        |     Lung Cancer     |
|       1       |    Secondary  |       1        | High Blood Pressure |
|       1       |    Secondary  |       2        |     Heart Attack    |
|       1       |    Secondary  |       3        |      Gastritis      |
|       2       |      Main     |       1        |      Diabetes       |
|       2       |    Secondary  |       1        |  Kidney Malfunction |
|       3       |      Main     |       1        |         Flu         |
+---------------+---------------+----------------+---------------------+

每个"主"DiagnosisType的DiagnosisOrder为1，而对于同一MedicalCardId的"辅助"DiagnosisType，它会重新启动以从1增加。

我想将同一MedicalCardId的多行合并为一行，每个Symptom根据其DiagnosisType和DiagnosisOrder成为一个新列

查询结果预计如下：

+---------------+-------------+---------------------+-------------------+-------------------+
| MedicalCardId | MainSymptom |  SecondarySymptom1  | SecondarySymptom2 | SecondarySymptom3 |
+---------------+-------------+---------------------+-------------------+-------------------+
|       1       | Lung Cancer | High Blood Pressure |    Heart Attack   |     Gastritis     |
|       2       |   Diabetes  |  Kidney Malfunction |                   |                   |
|       3       |     Flu     |                     |                   |                   |
+---------------+-------------+---------------------+-------------------+-------------------+

我试过使用PIVOT，但无法将其应用于我的实践。

Answer 1

您可以尝试条件聚合-

select MedicalCardId, 
max(case when DiagnosisType='Main' then Symptom end) as MainSymptom,
max(case when DiagnosisType='Secondary' and DiagnosisOrder=1 then Symptom end) as SecondarySymptom1,
max(case when DiagnosisType='Secondary' and DiagnosisOrder=2 then Symptom end) as SecondarySymptom2,
max(case when DiagnosisType='Secondary' and DiagnosisOrder=3 then Symptom end) as SecondarySymptom3
from tablename
group by MedicalCardId

Answer 2

我认为您需要创建一个动态透视表。不能使用普通透视表查询的原因是，您不知道有多少"次要症状"，因此也不知道要创建多少列。下面是一个有效的存储过程。第一步是创建一个VARCHAR(@Columns(变量，该变量将用于存储动态列名，这些列名将是[Main]、[Secondary1]、[Scondary2]、[Scendary3]等等(我使用了case语句来根据预期的查询结果创建列名(。第二步是创建另一个VARCHAR(@SQL(变量，该变量将包含透视表SQL查询。在这一步中，您将使用字符串串联将这个变量组合在一起。

Kris Wenzel在essentialsql.com上有一个关于动态数据透视表的很棒的教程，这里有链接https://www.essentialsql.com/create-dynamic-pivot-table-sql-server/

这是存储过程。

USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GenerateData]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--GATHER PIVOT COLUMNS DYNAMICALLY
DECLARE @Columns as VARCHAR(MAX)
SELECT @Columns =
COALESCE(@Columns + ', ','') + QUOTENAME([Diagnosis])
FROM
(SELECT DISTINCT case when [DiagnosisOrder] = 1 and [DiagnosisType] = 'Main' then 'MainSymptom' else 'SecondarySymptom' + CAST([DiagnosisOrder] AS VARCHAR) end [Diagnosis] FROM [TestDB].[dbo].[test] ) AS B
ORDER BY B.[Diagnosis]
--CREATE SQL QUERY FOR PIVOT TABLE
DECLARE @SQL as VARCHAR(MAX)
SET @SQL = 'SELECT MedicalCardId, ' + @Columns + '
FROM 
(
select [MedicalCardId]
,[Diagnosis]
,[Sympton]
from
(
SELECT [MedicalCardId]
,case when [DiagnosisOrder] = 1 and [DiagnosisType] = ''Main'' then ''MainSymptom'' else ''SecondarySymptom'' + CAST([DiagnosisOrder] AS VARCHAR) end [Diagnosis]
,[Sympton]
FROM [TestDB].[dbo].[test]
) A
) t 
PIVOT(
MAX([Sympton])
FOR [Diagnosis] IN (' + @Columns + ')
) AS pivot_table order by [MedicalCardId]'
--EXECUTE SQL
EXEC(@SQL)
END
GO