sql - Using Pivot with non Numerical Data -

this first time have ever tried use pivot. using microsoft sql server.

so here issue, have been reading on pivot , have decided work great project exports patient data formatted file i.e. report, can printed out etc.. etc..

vpatientplusallergydata view, displays sample result of data cut out 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 result want

 strpatientfullname strallergy1 strallergy1medications   strallergy2    strallergy2medications ------------------------------------------------------------------------------------------------------ smith, john henry   dogs        pounces, orangejuice     mustard        ketchup, sugar 

after readin on w3schools, watching youtube video , reading articles on site i'm wondering if trying possible

below code snippet got stuck on should put in in statement, , when started question viability of pivot being answer particular problem.

go select       strpatientfullname     ,strstreetaddress     ,strcity     ,strstate     ,strzipcode     ,strprimaryphonenumber     ,strsecondaryphonenumber     ,blnsmoker     ,decpackyears     ,blnheadofhousehold     ,dtmdateofbirth     ,strsex     ,strallergy     ,strallergymedication     ,stremailaddress     ,strrecordcreator  ( select * vpatientplusallergydata ) patientallergydata  pivot     (         max(strallergymedication)         strallergy         in ()     )  go 

hoping more familiar pivot show me missing or enlighten me more efficient solution.

thanks

****** edit: have decided while love put sort of operation on server side, particular application, simpler create ton of views perform select queries on client side , concatenate them way, implementing "export processing" screen. appreciate help, maybe on day write script , have execute server side, moment work enough ******

here's example of how stuff statement, conditional aggregation , dynamic sql.

declare @sql nvarchar(max) = ''; select @sql += '      , max(case when rn = ' + rn + ' strallergy end) strallergy' + rn + '      , max(case when rn = ' + rn + ' strallergymedications end) strallergymedications' + rn (     select cast(row_number() on (partition strpatientfullname, strallergy order (select null)) varchar(5)) rn     vpatientplusallergydata) t group rn;  select @sql = 'select strpatientfullname' + @sql + ' (     select strpatientfullname          , strallergy          , stuff((select '', '' + strallergymedication vpatientplusallergydata strpatientfullname = t.strpatientfullname , strallergy = t.strallergy xml path ('''')), 1, 2, '''') strallergymedications          , row_number() on (order (select null)) rn     vpatientplusallergydata t     group strpatientfullname, strallergy) t group strpatientfullname;';  print @sql; exec(@sql); 

as scsimon mentions in comments, dynamic sql may necessary if there can number of allergies. stuff statement 1 way of getting comma separated values single column. , conditional aggregation works in same way pivot work, far easier (imo) write , understand normal pivot statement.