iam using pivot table in SQL query.. i need to make some columns as rowss, but i need to use select statement in IN clause where we givt option like this
PIVOT( sum(target) FOR collectionName IN ( Select Ds.CollectionName as 'CollectionName' from v_DeploymentSummary Ds left join v_CIAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID left join v_AuthListInfo LI on LI.ModelID = Ds.ModelID Where Ds.FeatureType = 5 and Ds.CollectionName not like '%Windows 8%' and Li.Title like '%SUG_2020_06_P0_W7-W8-1_Critical%' )) AS pivot_table
normally we give option like
) t PIVOT( sum(target) FOR collectionName IN ( [W7-8.1 - Ring 2 - Laptops Wave 5], [W7-8.1 - Ring 2 - Laptops Wave 4], [W7-8.1 - Ring 2 - Laptops Wave 3], [W7-8.1 - Ring 2 - TARA -BI], [W7-8.1 - Ring 2 - Desktops], [W7-8.1 - Ring 2 - Laptops Wave 2], [W7-8.1 - Ring 2 - Laptops Wave 1], [W7 - Ring 1 - Early adopters], [W7 - Ring 0 - Fast ring] )) AS pivot_table
but with select statement its giving me error?? what can be done.
Advertisement
Answer
use this i got answer from some of google posts
select @cols = stuff( ( select distinct ',[' + Ltrim(rtrim(Ds.CollectionName)) +']' from v_DeploymentSummary Ds left join v_AuthListInfo LI on LI.ModelID = Ds.ModelID Where Ds.FeatureType = 5 and Ds.CollectionName not like '%Windows 8%' and Li.Title like @SUGname FOR XML PATH('')),1,1,'');