I'm not trying to eliminate the null values from table, so it's not duplicate of link [https://stackoverflow.com/questions/53205266/sql-server-concatenate-ignore-null-value][1]
How can I get result like OUTPUT table to be queried from original table?
I want to set the column values in alphabetical order and concatenate them as new column value.
Original table:
clsID stdID c1 c2 c3
---------------------------------------
cls1 10 HE HQ UA
cls1 11 HQ H1 UA
cls2 20 HG AB NULL
cls2 21 H2 HQ UA
cls2 22 NULL HQ UA
cls3 30 HG AB NULL
cls3 31 HQ GH UA
cls3 32 NULL HQ UA
cls3 33 NULL null null
In my output, I need to concatenate the columns stdId, c1, c2, c3, c4 and keep the new value as new column concatenatedvalue.
Resulting table:
clsID concatenatedvalue stdID c1 c2 c3
-----------------------------------------------------------
cls1 10-HE-HQ-UA 10 HE HQ UA
cls1 11-H1-HQ-UA 11 HQ H1 UA
cls2 20-AB-HG 20 HG AB NULL
cls2 21-H2-HQ-UA 21 H2 HQ UA
cls2 22-HQ-UA 22 NULL HQ UA
cls3 30-AB-HG 30 HG AB NULL
cls3 31-GH-HQ-UA 31 HQ GH UA
cls3 32-HQ-UA 32 NULL HQ UA
cls3 33 33 NULL null null
Since you need the concatenated value to be alphabetized, it would help to unpivot your data first and then string_agg() your data back together.
WITH unioncte (
SELECT clsid, stdid, c1 as col from table
union
SELECT clsid, stdid, c2 from table
union
SELECT clsID, stdID, c3 from table
)
SELECT unioncte.clsid,
CONCAT(stdID, STRING_AGG(col, '-') WITHIN GROUP (ORDER BY col) as concatinatedvalue,
unioncte.stdid,
t1.c1,
t1.c2,
t1.c3
FROM unioncte
INNER JOIN table as t1
ON unioncte.clsid = t1.clsid
AND unioncte.stdid = t1.stdid
GROUP BY clsid, stdid, c1, c2, c3