CODEWITHSUNDEEP
sqlsql-server
user2010107
user2010107

Reputation: 13

Combining SQL records that have the same the same value in a given field

Let's say, for example, that you have the following table

CourseId                             | CourseTitle | EntryMonth | EntryYear |
ebdef239-abb7-4a82-9229-1ed37496da86 | Maths FT    | January    | 2013      |
57504a66-4882-4794-a8b9-af0ead38dc70 | Maths FT    | February   | 2013      |
f06c5e58-5563-4dfd-a8fc-2ce186c2106f | Maths FT    | February   | 2014      |
0c81dfe6-0b11-4cad-a27c-970dbdb2876c | Maths FT    | February   | 2015      |
ebdef239-abb7-4a82-9229-1ed37496da86 | English PT  | January    | 2013      |
57504a66-4882-4794-a8b9-af0ead38dc70 | English PT  | January    | 2014      |

Is it possible to write a query that would group by CourseTitle & EntryMonth, but combine the EntryYear values into a temporary column (preferably comma delimited). So it would look this this:

CourseId                             | CourseTitle | EntryMonth | NewEntryYear     |
ebdef239-abb7-4a82-9229-1ed37496da86 | Maths FT    | January    | 2013             |
57504a66-4882-4794-a8b9-af0ead38dc70 | Maths FT    | February   | 2013, 2014, 2015 |
ebdef239-abb7-4a82-9229-1ed37496da86 | English PT  | January    | 2013, 2014       |

Any examples would be greatly appreciated. Thanks.

Upvotes: 1

Views: 90

Answers (2)

venki
venki

Reputation: 1131

If you are using oracle 10g or above, the below will work:

SELECT COURSEID, COURSETITLE, ENTRYMONTH, 
LISTAGG(ENTRYYEAR, ',') WITHIN GROUP (ORDER BY ENTRYYEAR) AS NEWENTRYYEAR
FROM YOUR_TABLE_NAME 
GROUP BY COURSEID, COURSETITLE, ENTRYMONTH

If you are not using Oracle 10g, this query would be of little help and I am sorry for that.

Upvotes: 0

Andomar
Andomar

Reputation: 238068

select  CourseTitle
,       EntryMonth
,       stuff((
        select  ', ' + cast(EntryYear as varchar)
        from    Table1 t2
        where   t1.CourseTitle = t2.CourseTitle
                and t1.EntryMonth = t2.EntryMonth
        for xml path('')
        ), 1, 2, '') as Years
from    Table1 t1
group by
        CourseTitle
,       EntryMonth

Example at SQL Fiddle.

Upvotes: 4

Related Questions