0

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

jarlh
  • 42,561
  • 8
  • 45
  • 63
Denzo37
  • 11
  • 2
  • Please show how you're pivoting in the first place? For example, it matters if the NULLs are always last or not. `x,y,z,NULL,NULL,NULL` vs `NULL,x,y,NULL,NULL,z`. – MatBailie Feb 23 '22 at 10:39
  • Hi there, the pivot would return non null values first and then null values last. So if there are 5 items, the first five columns would be populated and the remaining five columns would be NULL – Denzo37 Feb 23 '22 at 11:16
  • Sounds like you should scrap the `PIVOT` and use `STRING_AGG` instead, for example `SELECT PolicyKey, STRING_AGG(Item_Description, ' : ') FROM YourTable GROUP BY PolicyKey` – Charlieface Feb 23 '22 at 14:13
  • @Charlieface that function is only available in SSMS 2017 onwards, I'm still on 2012 – Denzo37 Feb 23 '22 at 15:14
  • OK you will have to use `FOR XML` or `FOR JSON` instead, see https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005. I strongly suggest you move to a supported version of SQL Server – Charlieface Feb 23 '22 at 15:18

1 Answers1

0

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 ' : '

MatBailie
  • 83,401
  • 18
  • 103
  • 137