Eliminate null results from case query - no null values in source

Question

I am trying to pull off a complicated aggregation and concatenation query to prep data for import to a website, aggregating multiple results rows and columns into one row per ID.

I'm almost there, except that I am getting a lot of NULL results despite there being no NULL values in the source data.

Source data:

id	value_1	value_2	value_3	type
x1	sometext	othertext	moretext	A2
x1	sometext	othertext	moretext	B1
x1	sometext	othertext	moretext	B2
x2	sometext	othertext	moretext	B1
x2	sometext	othertext	moretext	A2
x2	sometext	othertext	moretext	B1

The following:

select distinct id, 
case when type='A2' then string_agg (concat(cast (value_1 as nvarchar (max)),value_1,value_2,value_3)) end as type_A2, 
case when type='B1' then string_agg (concat(cast (value_1 as nvarchar (max)),value_1,value_2,value_3)) end as type_B1
from source
group by id, type

Produces:

id	type_a2	type_B1
x1	sometextothertextmoretext	NULL
x1	NULL	sometextothertextmoretext
x2	sometextothertextmoretext	NULL
x2	NULL	sometextothertextmoretext

When I try only running one column at a time, I still get some NULL results.

How do I get these results into one row per unique ID?

ie...

id	type_a2	type_B1
x1	sometextothertextmoretext	sometextothertextmoretext
x2	sometextothertextmoretext	sometextothertextmoretext
x3	etc	etc

Answer 1

You want one row per id - so, I would start by removing type from the select and group by clauses. Next, the case expression would go inside the aggregate functions. So:

select id,
    string_agg(case when type = 'A2' then concat(cast(value_1 as nvarchar(max)), value_1, value_2, value_3) end, ',') as type_a2,
    string_agg(case when type = 'B1' then concat(cast(value_1 as nvarchar(max)), value_1, value_2, value_3) end, ',') as type_b1
from mytable
group by id