Query that transpose multiple field values into 1 cell and categorize.

[world33]

Hello,

I am not sure if I can express this properly but I need to transpose and aggregate multiple field values (University programs) into 1 single cell for each University (UNITID) so that it lists all the programs offered by the University in one cell (with a manual line break Char(10) separating each program) and categorize them according to the degree level offered.

So to make it simplier to understand I have a query named Query1 that looks like this (the number of programs is much longer and I cut it for simplicity and better view):

UNITID	DegreeLevel	OfferedProgramsList
100654	Bachelor's degrees	Social Sciences
100654	Master's degrees	Agricultural/Animal/Plant/Veterinary Science and Related Fields
100654	Master's degrees	Architecture and Related Services
100654	Doctorate degrees	Agricultural/Animal/Plant/Veterinary Science and Related Fields
100663	Pre-bachelor's degrees	Business, Management, Marketing, and Related Support Services
100663	Bachelor's degrees	Area, Ethnic, Cultural, Gender, and Group Studies
100663	Bachelor's degrees	Biological and Biomedical Sciences
100663	Master's degrees	Communication, Journalism, and Related Programs
100663	Doctorate degrees	Biological and Biomedical Sciences

and I would like the Query1 to be transformed into another query named Query2 that looks like this:

UNITID	OfferedProgramsListAggregatedbyUniversityID
100654​	Bachelor's degrees Agricultural/Animal/Plant/Veterinary Science and Related Fields Architecture and Related Services Biological and Biomedical Sciences Business, Management, Marketing, and Related Support Services Communications Technologies/Technicians and Support Services Computer and Information Sciences and Support Services Education Engineering Engineering/Engineering-related Technologies/Technicians English Language and Literature/Letters Family and Consumer Sciences/Human Sciences Homeland Security, Law Enforcement, Firefighting and Related Protective Services Liberal Arts and Sciences, General Studies and Humanities Mathematics and Statistics Natural Resources and Conservation Parks, Recreation, Leisure, Fitness, and Kinesiology Physical Sciences Psychology Public Administration and Social Service Professions Social Sciences Visual and Performing Arts Master's degrees Agricultural/Animal/Plant/Veterinary Science and Related Fields Architecture and Related Services Biological and Biomedical Sciences Business, Management, Marketing, and Related Support Services Communication, Journalism, and Related Programs Computer and Information Sciences and Support Services Education Engineering Family and Consumer Sciences/Human Sciences Health Professions and Related Programs Parks, Recreation, Leisure, Fitness, and Kinesiology Physical Sciences Psychology Public Administration and Social Service Professions Agricultural/Animal/Plant/Veterinary Science and Related Fields Education Physical Sciences
100663​	Pre-bachelor's degrees Business, Management, Marketing, and Related Support Services Foreign Languages, Literatures, and Linguistics Health Professions and Related Programs Science Technologies/Technicians Bachelor's degrees Area, Ethnic, Cultural, Gender, and Group Studies Biological and Biomedical Sciences Business, Management, Marketing, and Related Support Services Communication, Journalism, and Related Programs Computer and Information Sciences and Support Services Education Engineering English Language and Literature/Letters Foreign Languages, Literatures, and Linguistics Health Professions and Related Programs History Homeland Security, Law Enforcement, Firefighting and Related Protective Services Liberal Arts and Sciences, General Studies and Humanities Mathematics and Statistics Philosophy and Religious Studies Physical Sciences Psychology Public Administration and Social Service Professions Social Sciences Visual and Performing Arts Master's degrees Biological and Biomedical Sciences Business, Management, Marketing, and Related Support Services Communication, Journalism, and Related Programs Computer and Information Sciences and Support Services Education Engineering English Language and Literature/Letters Health Professions and Related Programs History Homeland Security, Law Enforcement, Firefighting and Related Protective Services Mathematics and Statistics Multi/Interdisciplinary Studies Physical Sciences Psychology Public Administration and Social Service Professions Social Sciences Visual and Performing Arts Doctorate degrees Biological and Biomedical Sciences Computer and Information Sciences and Support Services Education Education Engineering Health Professions and Related Programs Health Professions and Related Programs Health Professions and Related Programs Mathematics and Statistics Multi/Interdisciplinary Studies Parks, Recreation, Leisure, Fitness, and Kinesiology Physical Sciences Psychology Social Sciences

As you can see all programs offered by the same University (UNITID) are aggregated into one cell and categorized (heading) according to the degree level.
In the actual query there are a few thousands of universities (UNITID) and around 52,000 data rows (programs) so the solution should be quick enough to process the query in a reasonable time.
I would appreciate any help in understanding the best solution for such a problem.
I attach a larger dataset in Excel with the first sheet showing the initial query format and a second sheet with the outcome I would like to achieve.

Thanks for any advice or help.

World33

 

[Gasman]

Use theDBguy's function.

Code Snippets

 

[arnelgp]

if you can share/upload a "real" tables/database.

 

[Minty]

A concatenated output within a query would, I suspect, be painfully slow.
However, a report could produce this easily. Have you considered building a report to give you this output?

 

[ebs17]

I have a query named Query1

You are free to be proud of your achievement, but it makes sense to start at the beginning, that is, deal with the database schema and the tables involved and their fields.
I don't have much confidence in an unknown query if it is to be processed further, even with a lot of data.