how to add select statement in IN clause in pivot

Question

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.

Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product (and PIVOT is non-standard SQL). Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Aug 07 '20 at 08:57
Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Aug 14 '20 at 18:13
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Aug 14 '20 at 18:14

score 0 · Accepted Answer · answered Aug 14 '20 at 15:37

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,'');

how to add select statement in IN clause in pivot

1 Answers1