Using Pivot with Odd Data

Question

This is the first time I've tried using PIVOT. I am using Microsoft SQL Server.

So, here is my problem, I read Pivot and decided that it is perfect for a project that exports patient data to a formatted file, i.e. report that can be printed, etc. etc.

VPatientPlusAllergyData is a VIEWER that displays it as a sample result with some data cut out for readability.

strPatientFullName  strAllergy  strAllergyMedication
------------------------------------------------------------
Smith, John Henry   Dogs        Pounces         
Smith, John Henry   Dogs        Orange Juice        
Smith, John Henry   Mustard     Ketchup         
Smith, John Henry   Mustard     Sugar           

As a result i want

 strPatientFullName strAllergy1 strAllergy1Medications   strAllergy2    strAllergy2Medications
------------------------------------------------------------------------------------------------------
Smith, John Henry   Dogs        Pounces, OrangeJuice     Mustard        Ketchup, Sugar

After reading through W3Schools, watching a Youtube video, and even reading some articles on this site, I wonder if what I'm trying to do is possible

- , , IN, PIVOT, .

GO
SELECT 
     strPatientFullName
    ,strStreetAddress
    ,strCity
    ,strState
    ,strZipcode
    ,strPrimaryPhoneNumber
    ,strSecondaryPhoneNumber
    ,blnSmoker
    ,decPackYears
    ,blnHeadOfHousehold
    ,dtmDateOfBirth
    ,strSex
    ,strAllergy
    ,strAllergyMedication
    ,strEmailAddress
    ,strRecordCreator

FROM ( SELECT * FROM VPatientPlusAllergyData ) PatientAllergyData

PIVOT
    (
        MAX(strAllergyMedication)
        FOR strAllergy
        IN ()
    )

GO

, -, Pivot, , , .

****** EDIT: , , ​​ , , SELECT , " ". , , script , ******

Answer 1

, - STUFF, SQL.

DECLARE @SQL NVARCHAR(MAX) = '';
SELECT @SQL += '
     , MAX(CASE WHEN RN = ' + RN + ' THEN strAllergy END) strAllergy' + RN + '
     , MAX(CASE WHEN RN = ' + RN + ' THEN strAllergyMedications END) strAllergyMedications' + RN
FROM (
    SELECT CAST(ROW_NUMBER() OVER (PARTITION BY strPatientFullName, strAllergy ORDER BY (SELECT NULL)) AS VARCHAR(5)) RN
    FROM VPatientPlusAllergyData) T
GROUP BY RN;

SELECT @SQL = 'SELECT strPatientFullName' + @SQL + '
FROM (
    SELECT strPatientFullname
         , strAllergy
         , STUFF((SELECT '', '' + strAllergyMedication FROM VPatientPlusAllergyData WHERE strPatientFullName = T.strPatientFullName AND strAllergy = T.strAllergy FOR XML PATH ('''')), 1, 2, '''') strAllergyMedications
         , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM VPatientPlusAllergyData T
    GROUP BY strPatientFullname, strAllergy) T
GROUP BY strPatientFullname;';

PRINT @SQL;
EXEC(@SQL);

scsimon , SQL , . stuff - , , . , PIVOT, (IMO) , PIVOT.

Answer 2

, , , :

• strAllergyMedications
• PIVOT, 2 , PIVOT

, , - , . Common Table Expression [CTE] STUFF() - XML .

DECLARE @VPatientPlusAllergyData AS TABLE (strPatientFullName  VARCHAR(100), strAllergy  VARCHAR(50), strAllergyMedication VARCHAR(100))
INSERT INTO @VPatientPlusAllergyData VALUES
('Smith, John Henry','Dogs','Pounces')
,('Smith, John Henry','Dogs','Orange Juice')
,('Smith, John Henry','Mustard','Ketchup')
,('Smith, John Henry','Mustard','Sugar')

;WITH cte AS (
    SELECT DISTINCT
       v1.strPatientFullName
       ,v1.strAllergy
       ,strAllergyMedications = STUFF(
          (SELECT ', ' + v2.strAllergyMedication
            FROM
                @VPatientPlusAllergyData v2
            WHERE
             v1.strPatientFullName = v2.strPatientFullName
             AND v1.strAllergy = v2.strAllergy
            FOR XML PATH(''))
            ,1,2,'')
       ,AllergyRowNum = DENSE_RANK() OVER (PARTITION BY v1.strPatientFullName ORDER BY v1.strAllergy)
    FROM
       @VPatientPlusAllergyData v1
)

SELECT
    strPatientFullName
    ,strAllergy1 = MAX(CASE WHEN AllergyRowNum = 1 THEN strAllergy END)
    ,strAllergy1Medications = MAX(CASE WHEN AllergyRowNum = 1 THEN strAllergyMedications END)
    ,strAllergy2 = MAX(CASE WHEN AllergyRowNum = 2 THEN strAllergy END)
    ,strAllergy2Medications = MAX(CASE WHEN AllergyRowNum = 2 THEN strAllergyMedications END)
FROM
    cte
GROUP BY
    strPatientFullName

@ZLK, .