SQL - concatenate values in a column

Here's my SQL:

DECLARE @dischargeSurveyId int = 4
DECLARE @dischargeTreatmentQuestionId int = 337373
SELECT p.Id, qc.Text
FROM Patient p CROSS JOIN SurveyResponse sr
INNER JOIN QuestionChoice qc ON qc.Id = sr.QuestionChoice_Id
-- limits responses to only those checked the most recent time the discharge survey was saved for the patient
WHERE p.Id = 1888887 AND sr.SurveyResponseSet_Id = (SELECT TOP 1 srs.Id
FROM SurveyResponseSet srs
inner join PatientSurveyVersion psv ON psv.Id = srs.PatientSurveyVersion_Id
inner join SurveyVersion sv ON sv.Id = psv.SurveyVersion_Id
WHERE sv.Survey_Id = @dischargeSurveyId AND psv.Patient_Id = p.Id ORDER BY srs.CreatedOn DESC)
AND qc.Question_Id = @dischargeTreatmentQuestionId

Open in new window


Got any idea how I can concatenate the qc.Text field for each Patient?  Data retrieved can be viewed in the attached pic.
Data.png
2_under_parAsked:
Who is Participating?
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.

Kevin CrossChief Technology OfficerCommented:
Hi.

First, I would rewrite the CROSS JOIN to use some of the functionality of new versions of SQL. CROSS APPLY or other techniques may also work, but see if the following returns the correct results regarding the qc.Text and p.Id values.
DECLARE @dischargeSurveyId int = 4
DECLARE @dischargeTreatmentQuestionId int = 337373

;WITH gc(Patient_Id, Question_Text, RN) AS (
    SELECT psv.Patient_Id, qc.Text
         /* Rank responses for each question for each patient based on date. */
         , ROW_NUBMER() OVER(PARTITION BY psv.Patient_Id, qc.Question_Id 
                             ORDER BY srs.CreatedOn DESC)
    FROM SurveyResponse sr
    INNER JOIN QuestionChoice qc ON qc.Id = sr.QuestionChoice_Id
    INNER JOIN SurveyResponseSet srs ON srs.Id = sr.SurveyResponseSet_Id
    INNER JOIN PatientSurveyVersion psv ON psv.Id = srs.PatientSurveyVersion_Id
    INNER JOIN inner join SurveyVersion sv ON sv.Id = psv.SurveyVersion_Id
    WHERE qc.Question_Id = @dischargeTreatmentQuestionId
    AND sv.Survey_Id = @dischargeSurveyId
)
SELECT p.Id, qc.Text
FROM Patient p 
INNER JOIN qc ON qc.Patient_Id = p.Id AND RN = 1
WHERE p.Id = 1888887
;

Open in new window


If yes, one solution to your question is to use a correlated sub-query and the FOR XML capabilities of SQL Server if your data does not present any restrictions to XML conversion. If it does, you can create a function that concatenates the values.
DECLARE @dischargeSurveyId int = 4
DECLARE @dischargeTreatmentQuestionId int = 337373

;WITH gc(Patient_Id, Question_Text, RN) AS (
    SELECT psv.Patient_Id, qc.Text
         /* Rank responses for each question for each patient based on date. */
         , ROW_NUBMER() OVER(PARTITION BY psv.Patient_Id, qc.Question_Id 
                             ORDER BY srs.CreatedOn DESC)
    FROM SurveyResponse sr
    INNER JOIN QuestionChoice qc ON qc.Id = sr.QuestionChoice_Id
    INNER JOIN SurveyResponseSet srs ON srs.Id = sr.SurveyResponseSet_Id
    INNER JOIN PatientSurveyVersion psv ON psv.Id = srs.PatientSurveyVersion_Id
    INNER JOIN inner join SurveyVersion sv ON sv.Id = psv.SurveyVersion_Id
    WHERE qc.Question_Id = @dischargeTreatmentQuestionId
    AND sv.Survey_Id = @dischargeSurveyId
)
SELECT p.Id
     , STUFF((SELECT ',' + qc.Question_Text
              FROM qc
              WHERE qc.Patient_Id = p.Id
              AND qc.RN = 1
              FOR XML PATH('')), 1, 1, '') AS [Text]
FROM Patient p 
WHERE p.Id = 1888887
;

Open in new window


I typed this by hand, so please forgive any type-o's. I hope it helps you get the gist, though.
0
2_under_parAuthor Commented:
I tried to correct what I thought might be typos and ran this.  I got 1 row, but it did not return all the values.  They were also not concatenated.

DECLARE @dischargeSurveyId int = 4
DECLARE @dischargeTreatmentQuestionId int = 337373

;WITH gc(Patient_Id, Question_Text, RN) AS (
    SELECT psv.Patient_Id, qc.Text
         /* Rank responses for each question for each patient based on date. */
         , ROW_NUMBER() OVER(PARTITION BY psv.Patient_Id, qc.Question_Id 
                             ORDER BY srs.CreatedOn DESC)
    FROM SurveyResponse sr
    INNER JOIN QuestionChoice qc ON qc.Id = sr.QuestionChoice_Id
    INNER JOIN SurveyResponseSet srs ON srs.Id = sr.SurveyResponseSet_Id
    INNER JOIN PatientSurveyVersion psv ON psv.Id = srs.PatientSurveyVersion_Id
    INNER JOIN SurveyVersion sv ON sv.Id = psv.SurveyVersion_Id
    WHERE qc.Question_Id = @dischargeTreatmentQuestionId
    AND sv.Survey_Id = @dischargeSurveyId
)
SELECT p.Id, gc.Question_Text
FROM Patient p 
INNER JOIN gc ON gc.Patient_Id = p.Id AND RN = 1
WHERE p.Id = 1888887
;

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
That query was not to get the concatenation, it was to validate we are getting the correct rows. I suspect we need a different PARTITION BY maybe? With an example of original data, I can help you through but to not get lost in the complexity, you can use the XML solution with what you had.

DECLARE @dischargeSurveyId int = 4
DECLARE @dischargeTreatmentQuestionId int = 337373

;WITH cte AS (
SELECT p.Id, qc.Text
FROM Patient p CROSS JOIN SurveyResponse sr
INNER JOIN QuestionChoice qc ON qc.Id = sr.QuestionChoice_Id
-- limits responses to only those checked the most recent time the discharge survey was saved for the patient
WHERE p.Id = 1888887 AND sr.SurveyResponseSet_Id = (SELECT TOP 1 srs.Id
FROM SurveyResponseSet srs
inner join PatientSurveyVersion psv ON psv.Id = srs.PatientSurveyVersion_Id
inner join SurveyVersion sv ON sv.Id = psv.SurveyVersion_Id
WHERE sv.Survey_Id = @dischargeSurveyId AND psv.Patient_Id = p.Id ORDER BY srs.CreatedOn DESC)
AND qc.Question_Id = @dischargeTreatmentQuestionId
)
SELECT p.Id
     , STUFF((SELECT ',' + t.[Text]
              FROM cte t
              WHERE t.Id = p.Id
              FOR XML PATH('')), 1, 1, '') AS [Text]
FROM cte p 
GROUP BY p.Id
;

Open in new window

0

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
2_under_parAuthor Commented:
mwvisa1,

That worked out and got the data in the attached pic... exactly as I needed.  Thank you!

Where you used the following, it seems foreign to me or at least above my current skill set.

1) The ";" characters
2) The "WITH cte AS" statement
3) The XML Path

Could you either elaborate on what you did there, or point me to a resource where I could read up on it, please?  

I will wait a bit for your answer, but will still accept the above as the solution.
Data-Returned.png
0
Kevin CrossChief Technology OfficerCommented:
Sorry I missed your follow-up questions, but I am glad the solution worked.

1) The ";" delimits the end of a SQL statement. In most cases you can leave those off, but in some database systems you cannot; therefore, it is just a habit. However, common table expressions, which I will discuss next, must not have a preceding un-closed statement. Therefore, as another habit, I start with ";" to make sure I close anything above I did not remember to put ";" on like the parameter declarations.

2) "WITH cte AS" is how you start a common table expression with the alias cte, which can be whatever you want (i.e., WITH my_table AS).
http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

3) FOR XML PATH is one of the ways to create XML in SQL.
http://technet.microsoft.com/en-us/library/ms178107.aspx
I use it here as a trick to get the data into a comma-delimited list because of how data elements work in XML.
0
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.