CODEWITHSUNDEEP
sql-servert-sql
nomen
nomen

Reputation: 3715

T-SQL: Aggregate a "collection" per row and "union" them

I have to write this question "conceptually" because I am not sure what approach to actually take. The way I am asking my question might hit the XY problem -- please suggest other alternatives if you see them.

I am writing a reporting query on a database that is already in production. Consider the following table (similar to my current result set):

Id     | Code1    | Code2    | Code3    |  Code4   | Code5
-----------------------------------------------------------
1        1          2          3           4         5
1        2          3          4           5         6
2        1          null       null        null      null

All of the codes are the same "type", both literally (as data types) and conceptually (as in, they represent the same "kind" of code, for the same kind of event, etc). Obviously, this data isn't properly normalized, but I still need to transform it.

I would like to write a query that "groups by" the id, and returns a collection of all the unique codes in the 5 code columns.

Ultimately, my goal will be to transform the table to:

Id      Codes
1       [1, 2, 3, 4, 5, 6]
2       [1]

(Where the [] at least conceptually represents some kind of collection or table or list type)

(Actually, I need to join against those codes for a quick look up, so the "actual" result should look like)

Id      CodeStrings
1       [Foo, Bar, Baz, Bon, Fizz, Buzz]
2       [Foo]

Can you describe a pattern that will let me form a "collection" per row, and aggregate them?

Upvotes: 0

Views: 86

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You need to unpivot and re-aggregate. This is pretty easy in SQL Server 2017+:

select t.id, string_agg(c.code, ',')
from t cross apply
     (select distinct v.code
      from (values (code1), (code2), (code3), (code4), (code5)
           ) v(code)
      where v.code is not null
     ) c;

In earlier versions, you can use for xml path:

select t.id, stuff(c.codes, 1, 1, '') as codes
from t cross apply
     (select distinct ',' + v.code
      from (values (code1), (code2), (code3), (code4), (code5)
           ) v(code)
      where v.code is not null
      for xml path ('')
     ) c(codes)

Upvotes: 1

reasonet
reasonet

Reputation: 145

I would unpivot the table first, then concatenate rows grouped by Id using For xml path.

Upvotes: 1

APH
APH

Reputation: 4154

For just 5, the easiest option might be:

Select ID, '[' + ISNULL(Code1, '') 
               + ISNULL(' ,' + Code2, '') 
               + ISNULL(' ,' + Code3, '') 
               + ISNULL(' ,' + Code4, '') 
               + ISNULL(' ,' + Code5, '') + ']'
From MyTable

Note that you might need to wrap the codes in a cast or convert to varchar if they are stored as numerics.

Upvotes: 0

Related Questions