SQL：有没有一种聪明的方法可以在没有内部查询或聚合函数的情况下在透视列中显示文本数据？

I have a bunch of data (multiple rows for each unique reference) that needs to be in one row with multiple columns. Some of columns that need to be used have to be further split out as they hold more than one value. This has been done using an unpivot. I now have 7 columns from this 1 original column and it now needs to display statuses against the new 7 columns. I cannot however use a pivot as I need to see the various statuses in the 7 columns and not a min, max or a count.I have a bunch of data (multiple rows for each