How to merge multiple rows into one row with filtering rules in SQL Server

Question

I have a table like this:

+---------------+---------------+----------------+---------------------+
| 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         |
+---------------+---------------+----------------+---------------------+

The DiagnosisOrder for each 'Main' DiagnosisType is 1, and for 'Secondary' DiagnosisType of the same MedicalCardId, it restarts to increase from 1.

I would like to merge multiple rows of the same MedicalCardId into a single row, and each Symptom becomes a new column depending on its DiagnosisType and DiagnosisOrder

The query result is expected to be like:

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

I've tried using PIVOT, but I'm unable to apply it to my practice.

Answer 1

You can try with conditional aggregation -

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

I believe you need to create a dynamic pivot table. The reason why you can’t use a normal pivot table query is because you don’t know how many Secondary Symptoms there are and therefore you don’t know how many columns to create. Below is a stored procedure that works. The first step is creating a VARCHAR (@Columns) variable that will be used to store the dynamic column names these will be [Main], [Secondary1], [Secondary2], [Secondary3] so on and so forth (I used a case statement to create the column names per your expected query result). The second step is creating another VARCHAR (@SQL) variable that will contain the pivot table SQL query. In this step you will use string concatenation to put this variable together.

Kris Wenzel has a great tutorial on dynamic pivot tables at essentialsql.com here is the link https://www.essentialsql.com/create-dynamic-pivot-table-sql-server/

Here is the stored procedure.

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