CODEWITHSUNDEEP
sqlsql-servert-sql
Art F
Art F

Reputation: 4202

Subquery results in comma separated format

I am trying to write a sub-query, that stores all the results in a single column separated by a comma. My code looks something like this

SELECT column1, 
       column2, 
       CourseRequests=(SELECT INNERCourseRequests = 
                              COALESCE(CASE 
                                         WHEN innercourserequests 
                                              = '' THEN 
                                         crse_name 
                                         ELSE innercourserequests 
                                              + ',' + 
                                              crse_name 
                                       END, '') 
                       FROM   tor_studentcrserequest SCR 
                       WHERE  SCR.stud_pk = MS.tt_stud_pk 
                              AND SCR.delt_flag = 0), 
       column4 
FROM   tbl_mainstudent MS

When I try to execute the stored procedure, I get an error saying Invalid column name 'INNERCourseRequests'.

What is the correct way to do this? TSR is a reference to table from the outer column

EDIT: I changed it to:

 CourseRequests=(SELECT INNERCourseRequests = 
 COALESCE(case when @INNERCourseRequests='' THEN CRSE_NAME ELSE
@INNERCourseRequests+','+CRSE_NAME end,'')

However, now I"m getting an error saying subquery returned more than 1 result which is expected.

Upvotes: 0

Views: 393

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48776

You can use FOR XML along with a few REPLACEs as shown here:

SELECT column1, 
       column2, 
       CourseRequests=COALESCE(
                        REPLACE(REPLACE(REPLACE((
                       SELECT crse_name 
                       FROM   (
                                         SELECT 1, 22,  'first',  0
                               UNION ALL SELECT 2, 22,  'second', 1
                               UNION ALL SELECT 3, 22,  'third',  0
                               UNION ALL SELECT 4, 555, 'first',  1
                       ) SCR (id, stud_pk, crse_name, delt_flag)
                       WHERE  SCR.stud_pk = MS.tt_stud_pk 
                       AND SCR.delt_flag = 0
                       FOR XML PATH('')
                       ),'</crse_name><crse_name>', ','),
                     '</crse_name>', ''), -- remove end tag
                     '<crse_name>', ''), -- remove beginning tag
                      ''), -- optional COALESCE to ensure no NULLs
       column4 
FROM   (
                   SELECT  1, 'a', 'b', '2014-01-01'
         UNION ALL SELECT 22, 'd', 'e', '2014-02-02'
       ) MS (tt_stud_pk, column1, column2, column4)

Output: 

column1 column2 CourseRequests  column4  
a       b                       2014-01-01  
d       e       first,third     2014-02-02

Explanation:
The FOR XML PATH('') flattens the result of the sub-query to be:
<crse_name>first</crse_name><crse_name>third</crse_name>

  1. The first REPLACE converts just the end-tag/beginning-tag combinations that are only found between values (i.e. where the commas go)
  2. The second REPLACE removes the ending tag (can't be done before the first REPLACE)
  3. The third REPLACE removes the beginning tag (can't be done before the first REPLACE)

Note:
There might be a slightly more elegant way to do the XML stuff so you don't need all of the REPLACEs, but not sure and this does work.

Upvotes: 1

Philip Kelley
Philip Kelley

Reputation: 40289

I'm pretty sure you can't do this with a single query, and I'm not entirely certain the tactic I've come up with is a legitimate tactic--meaning, if it is undocumented, a future version of SQL might not support this. With that said:

Start with the following:

DECLARE @List varchar(max)

SELECT @List = isnull(@List + ', ', '') + InnerCourseRequests
 from tor_studentcrserequest
 where stud_pk = <TestValue>
  and delt_flag = 0

PRINT @List

This will generate a comma-delimited list of all InnerCourseRequests from the tor_studentcrserequest table for a single stud_pk.

Next, turn it into a function:

DROP FUNCTION phkTest
GO
CREATE FUNCTION phkTest (@stud_pk  int)  --  Change datatype, if not int
RETURNS varchar(max)
AS
 BEGIN
    DECLARE @List varchar(max)

    SELECT @List = isnull(@List + ', ', '') + InnerCourseRequests
     from tor_studentcrserequest
     where stud_pk = @stud_pk
      and delt_flag = 0

    RETURN @List
 END
GO

(Add a second parameter for delt_flag, if that might vary somehow)

And add that to a query:

SELECT distinct tt_stud_pk, dbo.phkTest(stud_pk)
 from tbl_mainstudent

(I wrote all this using one of my tables, then cut-and-paste your table/columns in, so there may be some syntax issues to deal with.)

There may be ways to improve performance for big tables (OUTER APPLY, select distinct before calling the function, and so forth), and it's entirly likely that this might best be done via procedural code by whatever's querying the data in the first place.

Upvotes: 0

Related Questions