Remove comma if field is a blank value in concatenation in SQL - Automation Studio

Question

I am trying to concatenate BLOCK_ID1, BLOCK_ID2 and BLOCK_ID3 into one string in BLOCK_ID. I want to remove the last comma in the string.

MyDE

UNIQUE_ID	BLOCK_ID1	BLOCK_ID2	BLOCK_ID3
A	1	2	3
B	1	2
C	1

When i do:

SELECT UNIQUE_ID,
CONCAT(BLOCK_ID1,', ', BLOCK_ID2, ', ', BLOCK_ID3) as BLOCK_ID
From MyDE

OUTPUT DE:

UNIQUE_ID	BLOCK_ID
A	1, 2, 3
B	1, 2,
C	1,

I want to remove the last comma from B and C

Answer 1

Using the information that Marketing Cloud uses T-sql from Jonas Lamberty's answer, I figured a way to solve my problem.

Here is the code:

SELECT 
UNIQUE_ID,
CONCAT(BLOCK_ID1 + CASE WHEN BLOCK_ID2 = '' then '' else ', ' end,
BLOCK_ID2 + CASE WHEN BLOCK_ID3 = '' then '' else ', ' end,
BLOCK_ID3

) as BLOCK_ID

From myDE