SQL server Pivot Table columns from other table

Question

I have this pivot table:

select *
from TEST_COUNT_LINE_IMP
pivot (SUM (COUNT_CICLI) for Impiego in ([AT],[CL],[PD],[PR],[RC],[RW],[RV],[SP],[TR],[PV],[ST])) as T1

The list: [AT],[CL],[PD],[PR],[RC],[RW],[RV],[SP],[TR],[PV],[ST] come from another table:

SELECT AFCDIMPI
FROM ANA_IMPIEGHI

The question is:

Can I replace that list with a subquery? thanks

Answer 1

I think you should use dynamic query in this case. I have an example of this below, hope it will help you:

DECLARE @statement NVARCHAR(max)
,@columns NVARCHAR(max)

SELECT @columns = ISNULL(@columns + ', ', '') + '[' + tbl.ColName + ']'
FROM (
   SELECT DISTINCT ColName
   FROM TableName
   ) AS tbl

SELECT @statement = ' SELECT * FROM TableName as tbl PIVOT (MAX(tbl.VALUE) for tbl.ColName in (' + @columns + ')) as pvt'

EXEC sp_executesql @statement = @statement