Combine two columns and aggregate into one value

Question

Combine two columns in SQL Server: one column has varchar values and other column has int values. enter image description here .

This is the code I have used to get the result

Trying to combine two columns: one column is varchar and another one is int.

I'm trying to combine learningareas and Noramlyearlevel columns.

This is the actual query.

select distinct v.StaffPreferredName, v.StaffSurname, v.StaffOccupEmail, 
l.learningareacode as 'Learning Areas',
sc.NormalYearLevel as 'NormalYearLevel'
from vstaffDescription'

And I'm trying to get the result output like this

enter image description here .

I tried this code,

enter image description here

I got the output as,

Alexander   Scott   Alex.Scott@pegs.vic.edu.au  Geography: 8, Geography: 8, Geography: 8, Politics: 10, Politics: 10.

Please guide me to get the required output.

Thanks

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Jun 19 '23 at 05:03
There are a handful of other similar StackOverflow questions, or else just "sql string_agg unique values". https://stackoverflow.com/questions/50589064/get-unique-values-using-string-agg-in-sql-server https://stackoverflow.com/questions/68576922/sql-how-to-get-distinct-values-out-of-string-agg-function https://stackoverflow.com/questions/70782361/how-do-i-use-string-agg-and-avoid-duplicate-values — Craig, Jun 19 '23 at 05:14

score 0 · Answer 1 · answered Jun 19 '23 at 06:29

Please avoid images when forming questions - and also do include the complete query you have tried (it seems you omitted some parts). In essence what you want to do is remove the unwanted repetition of data before you use string_agg e.g:

SELECT
      derived.StaffPreferredName
    , derived.StaffSurname
    , derived.StaffOccupEmail
    , STRING_AGG(serived.learningAreaYearLevel, ',') WITHIN GROUP ( ORDER BY learningAreaYearLevel ) AS learningAreaYearLevel
FROM (
    SELECT DISTINCT
          v.StaffPreferredName
        , v.StaffSurname
        , v.StaffOccupEmail
        , CONCAT (l.learningareacode , ' ' , sc.NormalYearLevel) AS learningAreaYearLevel
    FROM vstaffDescription AS v
    INNER JOIN mystery_table1 AS l ON v.x = l.y
    INNER JOIN mystery_table2 AS sc ON v.x = sc.y
    ) AS derived
GROUP BY
      derived.StaffPreferredName
    , derived.StaffSurname
    , derived.StaffOccupEmail

You will need to repair the missing table references and joins.

Combine two columns and aggregate into one value

1 Answers1