SQL - Need to show Insurance Name as comma separated

I have the following SQL report that I am working on.  The field AIO.InsuranceName is showing up as each insurance that the patient has in a separate row.  I need it to show,  

Name            ServiceDate   AppointmentTypeID  InsuranceName                         ProviderID  PrimaryCarePhy
DOE,JOHN     07/01/2015    PMEVAL                       COVENTRY , BC OF IL PPPO      KADCA         Jane Doe, MD

Here is my code:

ALTER PROCEDURE [dbo].[spPainMgmtNPReferrals_kr]
	@BeginDate DATETIME,
	@EndDate DATETIME
	
AS

Select Distinct

AV.Name,
CONVERT (VARCHAR (10), SA.DateTime, 101) as ServiceDate,
SA.AppointmentTypeID,
AIO.InsuranceName,
SA.ProviderID,
DMU.ProviderName AS 'PrimaryCarePhy'


FROM livendb.dbo.AdmVisits AV (NOLOCK) --select * from livendb.dbo.AdmVisits

LEFT JOIN livendb.dbo.SchAppointments  SA (NOLOCK)  --Select * from livendb.dbo.SchAppointments
	ON AV.SourceID = SA.SourceID
	AND AV.VisitID = SA.VisitID
	
LEFT JOIN livendb.dbo.AdmInsuranceOrder  AIO (NOLOCK) --select * from livendb.dbo.AdmInsuranceOrder
            On AV.SourceID = AIO.SourceID
            and AV.VisitID = AIO.VisitID

LEFT JOIN livendb.dbo.AdmProviders AP (NOLOCK) -- Select * from livendb.dbo.AdmProviders
	ON AV.SourceID = AP.SourceID
	AND AV.VisitID = AP.VisitID
	

	LEFT JOIN livendb.dbo.DMisUsers DMU -- Select * from livendb.dbo.DMisUsers
		on AP.SourceID = DMU.SourceID
		AND AP.PrimaryCareID = DMU.ProviderID
		
Where CONVERT (VARCHAR (10), SA.DateTime, 101)BETWEEN @BeginDate AND @EndDate
and SA.FacilityID='BMH'
and SA.LocationID = 'H.PMC'
AND SA.ProviderID IN ('ANDAN' ,'KADCA')
AND SA.AppointmentTypeID = 'PMEVAL'


ORDER BY 2

Open in new window


Thanks,
KR
kvrogersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
Here's a suggested FROM clause which changes the way you work with the insurer information to an OUTER APPLY, plus changes to an INNER JOIN for SchAppointments.
      FROM livendb.dbo.AdmVisits AV (NOLOCK) --select * from livendb.dbo.AdmVisits

      INNER JOIN livendb.dbo.SchAppointments SA (NOLOCK)  --Select * from livendb.dbo.SchAppointments
            ON AV.SourceID = SA.SourceID
            AND AV.VisitID = SA.VisitID
      OUTER APPLY (
            SELECT
                  STUFF((
                        SELECT
                              ', ' + i.InsuranceName
                        FROM livendb.dbo.AdmInsuranceOrder AS i
                        WHERE AV.SourceID = i.SourceID
                        AND AV.VisitID = i.VisitID
                        FOR xml PATH ('')
                  )
                  , 1, 1, '')
      ) AIO (InsuranceName)

      LEFT JOIN livendb.dbo.AdmProviders AP (NOLOCK) -- Select * from livendb.dbo.AdmProviders
            ON AV.SourceID = AP.SourceID
            AND AV.VisitID = AP.VisitID


      LEFT JOIN livendb.dbo.DMisUsers DMU -- Select * from livendb.dbo.DMisUsers
            ON AP.SourceID = DMU.SourceID
            AND AP.PrimaryCareID = DMU.ProviderID

Open in new window

The reason for the change to inner join is that your WHERE clause does not allow any NULL values from that table; so you are already getting the effect of an inner join anyway. It's just more efficient to specify the inner join.

The OUTER APPLY logic puts all insurer's for a row into a comma separated string (that's done by the FOR XML PATH)  and STUFF() just removes the unwanted first comma.

{+edit}
=============================
Please don't alter the data to suit the filtering. It is not necessary and it is more efficient to change the filter criteria to suit the data.

i..e
do NOT convert a datetime column to varchar, then compare thet to dates or datetime values,
this is inefficient

      WHERE CONVERT(varchar(10), SA.DateTime, 101) BETWEEN @BeginDate AND @EndDate

Do this instead:

      WHERE ( SA.DateTime >= @BeginDate AND SA.DateTime < dateadd(day,1,@EndDate) )

     here the data is left unchanged (saving effort) and it avoids the varchar to datetime comparisons too

Also note BETWEEN is NOT best practice for date ranges, for more on this topic please see: "Beware of Between"

the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.
Itzik Ben-Gan

{edit}
Removed an incorrectly placed between

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kvrogersAuthor Commented:
When I use the WHERE clause for date I get this error:

Msg 156, Level 15, State 1, Procedure spPainMgmtNPReferrals_kr, Line 57
Incorrect syntax near the keyword 'BETWEEN'.

This is code I used:
WHERE (SA.DateTime >= BETWEEN @BeginDate AND SA.DateTime < dateadd(day,1,@EndDate) )

KR
PortletPaulEE Topic AdvisorCommented:
Remove the word BETWEEN
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

PortletPaulEE Topic AdvisorCommented:
Apologies, I left it there in my earlier comment. It should not be included.
kvrogersAuthor Commented:
Thank you.  That did fix the report and allow it to run.

Now when the report runs,  the Medicare insurance is combing but I get this in the column.

 MEDICARE PARTS A &amp; B, TRICARE FOR LIFE

this is only for Medicare Parts A & B.  Will this Ampersand go away in Visual Studio?
PortletPaulEE Topic AdvisorCommented:
Mmmmmm, drat

That's an outcome of using an XML feature, it has used an XML entity for the ampersand character.

Don't know what impact visual studio will have.

I can't error at this until tomorrow, sorry
kvrogersAuthor Commented:
This was soooo helpful.  I keep all of my posts and will refer to this one I think often.   I did tell them I had help and did not pawn this off as my own work.    They are just grateful I have another resource to refer to.

Thanks Again,
KR
kvrogersAuthor Commented:
I am going to have to put this in Visual Studio as they want the report run every month and I can have it run and then emailed to them monthly.  I was wondering if there was a way I could create an expression in Visual Studio for the Insurance Name field that would remove the "&amp;" from the results.

KR
PortletPaulEE Topic AdvisorCommented:
Thanks for the points and grade. I'm very sorry however that I wasn't able to get back to you earlier on this topic. There is a better way IMHO.

The construct for giving you the comma separated string is FOR XML PATH and because it is XML related there are a series of special characters that XML will convert into an "entity" e.g. & become &amp; and there are several more of these than just the ampersand.

So, there are some additional parameters that can be used with the FOR XML PATH that will  alter the XML Entity behaviour so that you do not see "&amp;" when the data contains "&"
            SELECT
                  STUFF((
                        (SELECT
                              ', ' + i.InsuranceName
                        FROM dbo.AdmInsuranceOrder AS i
                        WHERE AV.SourceID = i.SourceID
                        AND AV.VisitID = i.VisitID
                        FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')
                  )
                  , 1, 1, '')

Open in new window

Replace the CROSS APPLY content with the subquery above, this should solve that problem.

---
By the way, TSQL also has a replace function:
REPLACE ( string_expression , string_pattern , string_replacement )
kvrogersAuthor Commented:
Paul:

I replaced the script as:

INNER JOIN livendb.dbo.SchAppointments SA (NOLOCK)  --Select * from livendb.dbo.SchAppointments
            ON AV.SourceID = SA.SourceID
            AND AV.VisitID = SA.VisitID
     
        SELECT
                  STUFF((
                        (SELECT
                              ', ' + i.InsuranceName
                        FROM dbo.AdmInsuranceOrder AS i
                        WHERE AV.SourceID = i.SourceID
                        AND AV.VisitID = i.VisitID
                        FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')
                  )
                  , 1, 1, '')

and got this error:

"Incorrect syntax near the keyword 'LEFT'."
This is the next  LEFT JOIN.  I removed the  OUTER APPLY also.  

At the end of the script above it had :     ) AIO (InsuranceName)
I thought that was the problem and put it back in and got an error of:
"Incorrect syntax near ')'.   in that line."

KR
kvrogersAuthor Commented:
I also tried this:

  OUTER APPLY (
              SELECT
                  STUFF((
                        (SELECT
                              ', ' + i.InsuranceName
                        FROM dbo.AdmInsuranceOrder AS i
                        WHERE AV.SourceID = i.SourceID
                        AND AV.VisitID = i.VisitID
                        FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')
                  )
                  , 1, 1, '')
      ) AIO (InsuranceName)

But the Insurance name came up as all NULL.

KR
PortletPaulEE Topic AdvisorCommented:
      FROM livendb.dbo.AdmVisits AV (NOLOCK) --select * from livendb.dbo.AdmVisits

      INNER JOIN livendb.dbo.SchAppointments SA (NOLOCK)  --Select * from livendb.dbo.SchAppointments
            ON AV.SourceID = SA.SourceID
            AND AV.VisitID = SA.VisitID
      OUTER APPLY (
            SELECT
                  STUFF((
                        (SELECT
                              ', ' + i.InsuranceName
                        FROM dbo.AdmInsuranceOrder AS i
                        WHERE AV.SourceID = i.SourceID
                        AND AV.VisitID = i.VisitID
                        FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')
                  )
                  , 1, 1, '')
      ) AIO (InsuranceName)

      LEFT JOIN livendb.dbo.AdmProviders AP (NOLOCK) -- Select * from livendb.dbo.AdmProviders
            ON AV.SourceID = AP.SourceID
            AND AV.VisitID = AP.VisitID


      LEFT JOIN livendb.dbo.DMisUsers DMU -- Select * from livendb.dbo.DMisUsers
            ON AP.SourceID = DMU.SourceID
            AND AP.PrimaryCareID = DMU.ProviderID

Open in new window

kvrogersAuthor Commented:
I entered the above script and got no error but the insurance column shows NULL for every patient.

KR
PortletPaulEE Topic AdvisorCommented:
what does it do when seperated?

            SELECT
                  STUFF((
                        (SELECT
                              ', ' + i.InsuranceName
                        FROM dbo.AdmInsuranceOrder AS i

                      where <<you provide some limiting filter here please>>

                        FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')
                  )
                  , 1, 1, '')

Open in new window

kvrogersAuthor Commented:
When I use the first select:
STUFF((
                        SELECT
                              ', ' + i.InsuranceName
                        FROM livendb.dbo.AdmInsuranceOrder AS i
                        WHERE AV.SourceID = i.SourceID
                        AND AV.VisitID = i.VisitID
                        FOR xml PATH ('')
                  )
                  , 1, 1, '')
      ) AIO (InsuranceName)

I get the results that are attached on this spreadsheet.  I just need the &amp removed from the report which is under the InsuranceName field.

KR
H--Insurance-name.xlsx
PortletPaulEE Topic AdvisorCommented:
Yep, I do understand the problem, no need to supply more example output from the faulty query.

It would be more helpful to know if running the standalone query I last added worked or not. Even more helpful would be a place where we could collaborate using a sample set of data.

See if this link is working please. http://sqlfiddle.com/#!3/1b843f/7

on the right of the page there are 2 queries, one which produces "&amp;" in the output, and another which does not do that.

Details of these here:
**MS SQL Server 2008 Schema Setup**:

    CREATE TABLE AV
        ([SourceID] int)
    ;
        
    INSERT INTO AV
        ([SourceID])
    VALUES
        (1),
        (2),
        (3),
        (4),
        (5)
    ;
    
    
    CREATE TABLE AdmInsuranceOrder 
        ([SourceID] int, [InsuranceName] varchar(40))
    ;
        
    INSERT INTO AdmInsuranceOrder 
        ([SourceID], [InsuranceName])
    VALUES
        (1, 'RAILROAD MCR PART AB'),
        (2, 'CIGNA PPO'),
        (3, 'BC OF IL PPO'),
        (4, 'BELLEVILLE MEMORIAL HOS'),
        (5, 'MED REIMB AMER WC'),
        (1, 'MEDICARE PARTS A & B'),
        (2, 'UNITED HEALTH CARE'),
        (3, 'OTHER2'),
        (4, 'RAILROAD MCR PART AB'),
        (5, 'CIGNA PPO'),
        (1, 'BC OF IL PPO'),
        (2, 'BELLEVILLE MEMORIAL HOS'),
        (3, 'MED REIMB AMER WC'),
        (4, 'MEDICARE PARTS A & B'),
        (5, 'UNITED HEALTH CARE')
    ;
    
**Query 1**:

    SELECT
          *
    FROM av
    CROSS APPLY (
          SELECT
                STUFF((
                      SELECT
                            ', ' + i.InsuranceName
                      FROM AdmInsuranceOrder AS i
                      WHERE AV.SourceID = i.SourceID
                      --AND AV.VisitID = i.VisitID
                      FOR xml PATH ('')
                )
                , 1, 1, '')
    ) AIO (InsuranceName)
    

**[Results][2]**:
    | SourceID |                                                            InsuranceName |
    |----------|--------------------------------------------------------------------------|
    |        1 |             RAILROAD MCR PART AB, MEDICARE PARTS A &amp; B, BC OF IL PPO |
    |        2 |                   CIGNA PPO, UNITED HEALTH CARE, BELLEVILLE MEMORIAL HOS |
    |        3 |                                  BC OF IL PPO, OTHER2, MED REIMB AMER WC |
    |        4 |  BELLEVILLE MEMORIAL HOS, RAILROAD MCR PART AB, MEDICARE PARTS A &amp; B |
    |        5 |                         MED REIMB AMER WC, CIGNA PPO, UNITED HEALTH CARE |
**Query 2**:

    
    
    SELECT
          *
    FROM av
    CROSS APPLY (
          SELECT
                STUFF(((
                      SELECT
                            ', ' + i.InsuranceName
                      FROM dbo.AdmInsuranceOrder AS i
                      WHERE AV.SourceID = i.SourceID
                      --AND AV.VisitID = i.VisitID
                      FOR xml PATH (''), TYPE
                )
                .value('(./text())[1]', 'NVARCHAR(MAX)')
                )
                , 1, 1, '')
    ) AIO (InsuranceName)
    

**[Results][3]**:
    | SourceID |                                                        InsuranceName |
    |----------|----------------------------------------------------------------------|
    |        1 |             RAILROAD MCR PART AB, MEDICARE PARTS A & B, BC OF IL PPO |
    |        2 |               CIGNA PPO, UNITED HEALTH CARE, BELLEVILLE MEMORIAL HOS |
    |        3 |                              BC OF IL PPO, OTHER2, MED REIMB AMER WC |
    |        4 |  BELLEVILLE MEMORIAL HOS, RAILROAD MCR PART AB, MEDICARE PARTS A & B |
    |        5 |                     MED REIMB AMER WC, CIGNA PPO, UNITED HEALTH CARE |

http://sqlfiddle.com/#!3/1b843f/7

Open in new window

Please note that the number of parentheses changes in the 2 subqueries e.g.
STUFF ((
STUFF ((( --<< one more in 2nd query

so if copying, make sure you copy everything between

    CROSS APPLY (
    ) AIO (InsuranceName)

plus
as the example data is very simple part of the where clause has been commented out by --
and a table reference is abbreviated to just dbo.AdmInsuranceOrder
kvrogersAuthor Commented:
I am sorry if I was not explaining myself correctly.  I thought on 7/31  at 09:34:56  I had indicated that using the script you had provided above that showed results in the Insurance Name column of NULL for all values.

I entered Query #2 as above but it ran too long.  Then I noticed that the AND AV.VISITID= i.VisitID had been commented out.  When I put it back in and ran it the report completed fast and correctly.  

What is the difference between using the CROSS APPLY versus the OUTER APPLY?  
I tried running with both and got the same results but wanted to know the difference.

Was the  other problem I had  was that  I needed an extra ( after STUFF in the CROSS APPLY?   so it would look like:  STUFF ( ( (  

Thank you for your time,
KR
PortletPaulEE Topic AdvisorCommented:
You explained yourself quite clearly, absolutely no need to apologize for that, and I did understand the issues. If I seemed gruff at all I do apologize, but it is impossible for me to see what is happening at your end.

>>"Then I noticed that the AND AV.VISITID= i.VisitID had been commented out.  "
Oh dear, yes. In my defense I did try to point that out, but I'm very glad you got there in the end.

What is the difference between CROSS/OUTER APPLY?
in essence:
CROSS APPLY is similar to an INNER JOIN
OUTER APPLY is similar to a LEFT OUTER JOIN
here is a reference that may help.
https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

>>"Was the  other problem I had  ..."
I am unable to know why you were getting NULLs because I did not see the actual query you were running at the time.

I hope you will understand the following is not a complaint, but when a person takes information from suggestions made and then says "there's a problem", all we know is that there is a problem.

It may be due to an error made in the suggestions (like my original suggestion), or an error introduced while trying to implement the suggestion. And there could be multiples of these if changes we haven't suggested are made to avoid the error. Hence it can get frustrating. I have seen some question threads get wildly out of whack as error messages are passed back to us, but the code we think that is being run is not anything like the actual code being used. It can get even wilder if there are multiple contributors too as comments can overlap and in the end it's like tracing the path of spaghetti in a cup.

the bottom line:
You have seen that "FOR XML PATH" allows you to generate comma separated strings which can be  prepared using the APPLY operator. To avoid XML entities there is a very particular and fussy syntax that can be used, if that syntax isn't 100% then odd things (such as NULLs) can happen.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.