SQL - Return Only Non Null Values From Multiple Columns After a Pivot

Question

My data has multiple items per policy key with one row per item like the below. The number of items can vary per policy key up to a max of 10 items.

PolicyKey	Item_Description
1234567	Bike
1234567	IPhone
1234567	Wedding Ring

I am using a pivot to group items up per policy key and pivot into multiple columns to get this result

PolicyKey	[1]	[2]	[3]
1234567	Bike	IPhone	Wedding Ring

As I don't want to select potentially 10 columns from my pivot (i.e. one for each item description) I am concatenating these into one column in my SELECT statement as follows:

SELECT CONCAT(c.[1],' : ',c.[2],' : ',c.[3],' : ',c.[4],' : ',c.[5],' : ',c.[6],' : ',c.[7],' : ',c.[8],' : ',c.[9],' : ',c.[10])) AS AllItems

The problem is for those policy keys where the number of items is say one or two, my CONCAT statement will include a number of ' : ' characters (i.e. the seperator in my CONCAT statement) at the end of the cell.

Is there any function that can return only the non-null values in multiple columns? Because the result of my pivot can potentially have say 1 non null column and 9 null columns, I'd like to be able to select only the non-null columns from the list of 10.

I should mention I am working in SSMS 2012 so am not able to use any of the new functions available in later version of SSMS

Thanks in advance

Answer 1

A brute force approach would be...

CONCAT(
  c.[1],
 (' : ' + c.[2]),
 (' : ' + c.[3]),
  ...
)

When using + rather than CONCAT(), if a column is NULL, the ' : ' + col will yield NULL, rather than ' : '