CODEWITHSUNDEEP
sqlsql-server-2008
Flater
Flater

Reputation: 13783

SQL - combine two columns into a comma separated list

The problem I'm facing is probably easy to fix, but I can't seem to find an answer online due to the specificity of the issue.

In my database, I have a 3 tables to denote how an educational course is planned. Suppose there is a course called Working with Excel. This means the table Courses has a row for this.

The second table denotes cycles of the same course. If the course is given on Jan 1 2013 and Feb 1 2013, in the underlying tables Cycles, you will find 2 rows, one for each date.

I currently already have an SQL script that gives me two columns: The course name, and a comma separated list with all the Cycle dates.

Please note I am using dd/MM/yyyy notation

This is how it's currently set up (small excerpt, this is the SELECT statement to explain the desired output):

SELECT course.name,
       stuff((SELECT distinct ',' + CONVERT(varchar(10), cycleDate, 103)    --code 101 = mm/dd/yyyy, code 103 = dd/mm/yyyy
                            FROM cycles t2
                            where t2.courseID= course.ID and t2.cycleDate > GETDATE()
                            FOR XML PATH('')),1,1,'') as 'datums'

The output it gives me:

NAME                  DATUMS
---------------------------------------------------
Working with Excel    01/01/2013,01/02/2013
Some other course     12/3/2013, 1/4/2013, 1/6/2013

The problem is that I need to add info from the third table I haven't mentioned yet. The table ExtraDays contains additional days for a cycle, in case this spans more than a day.

E.g., if the Working with Excel course takes 3 days, (Jan 1+2+3 and Feb 1+2+3), each of the course cycles will have 2 ExtraDays rows that contain the 'extra days'.

The tables would look like this:

Table COURSES
ID   NAME
---------------------------------------------------
1    Working with Excel 

Table CYCLES
ID   DATE          COURSEID
---------------------------------------------------
1    1/1/2013      1
2    1/2/2013      1

Table EXTRADAYS
ID   EXTRADATE     CYCLEID
---------------------------------------------------
1    2/1/2013      1
2    3/1/2013      1
3    2/2/2013      2
4    3/2/2013      2

I need to add these ExtraDates to the comma-separated list of dates in my output. Preferably sorted, but this is not necessary.

I've been stumped quite some time by this. I have some SQL experience, but apparently not enough for this issue :)

I'm hoping to get the following output:

NAME                  DATUMS
--------------------------------------------------------------------------------------
Working with Excel    01/01/2013,02/01/2013,03/01,2013,01/02/2013,02/02/2013,03/02/2013

I'm well aware that the database structure could be improved to simplify this, but unfortunately this is a legacy application, I cannot change the structure.

Can anyone point me in the right way to combining these two columns.

I hope I described my issue clear enough for you. Else, just ask :)

Upvotes: 2

Views: 4562

Answers (1)

paul
paul

Reputation: 22001

SELECT course.name,
       stuff((SELECT distinct ',' + CONVERT(varchar(10), cycleDate, 103)    --code 101 = mm/dd/yyyy, code 103 = dd/mm/yyyy
              FROM (select id, date, courseid from cycles 
                    union 
                    select id, extradate, courseid from extradays) t2
              where t2.courseID= course.ID and t2.cycleDate > GETDATE()
              FOR XML PATH('')),1,1,'') as 'datums'

Upvotes: 3

Related Questions