user3119737 user3119737 - 25 days ago 7
SQL Question

Using Pivot with non Numerical Data

This is the first time I have ever tried to use PIVOT.
I am using Microsoft SQL Server.

So here is my issue, I have been reading up on Pivot and have decided that it would work great for a project that exports Patient data to a formatted file i.e. Report, that can be printed out etc.. etc..

VPatientPlusAllergyData is a VIEW, that displays this as a sample result with some of the data cut out for ease of reading

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


This is the result I want

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


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

below is a code snippet but I got stuck on what I should put in the IN statement, and when I started to question the viability of PIVOT being the answer to my particular problem.

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


Hoping someone more familiar with Pivot will show me what I am missing or enlighten me to a much more efficient solution.

Thanks for the help

****** EDIT: I Have Decided that while I would love to put this sort of operation on the server side, for my particular application, it was just simpler to create a ton of views then perform SELECT queries on the client side and concatenate them that way, then implementing a "EXPORT PROCESSING" Screen.
I appreciate all the help, maybe on day I will write a script and have it execute server side, but for the moment this work good enough ******

ZLK ZLK
Answer

Here's an example of how you could do something like this with a STUFF statement, conditional aggregation and dynamic 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);

As scsimon mentions in the comments, dynamic SQL may be necessary if there can be any number of allergies. A stuff statement is one way of getting the comma separated values into a single column. And the conditional aggregation works in the same way that a PIVOT would normally work, but is far easier (IMO) to write and understand than a normal PIVOT statement.