Using "select statement" instead of "dynamic column" on PIVOT

Question

Here is my current query on pivot

SELECT CORNo, [0001], [0002], [0003]
FROM 
    (
        Select InvoiceNo, CORNo as CORNo, Amount
        from InvoiceItems II 
        left join Invoices I on I.InvoiceID = II.InvoiceID 
        left join CORProjects CPI on CPI.CORProjectID = II.ReferenceID and CPI.ProjectID = I.ProjectID
        LEFT JOIN Projects P ON P.ProjectID = CPI.ProjectID
        where II.ReferenceID = CPI.CORProjectID and I.InvoiceType = 2
        and P.ProjectID = 913 
    ) AS t1 
PIVOT 
(
    MAX(Amount) 
    FOR InvoiceNo IN ( [0001], [0002], [0003]) 
) AS t2

What i want to do is not to use the dynamic column ( [0001], [0002], [0003]) and use select query in "IN" clause.

SELECT CORNo, select distinct(InvoiceNo) from Invoices where ProjectID = 913
FROM 
    (
        Select InvoiceNo, CORNo as CORNo, Amount
        from InvoiceItems II 
        left join Invoices I on I.InvoiceID = II.InvoiceID 
        left join CORProjects CPI on CPI.CORProjectID = II.ReferenceID and CPI.ProjectID = I.ProjectID
        LEFT JOIN Projects P ON P.ProjectID = CPI.ProjectID
        where II.ReferenceID = CPI.CORProjectID and I.InvoiceType = 2
        and P.ProjectID = 913 
    ) AS t1 
PIVOT 
(
    MAX(Amount) 
    FOR InvoiceNo IN (select distinct(InvoiceNo) from Invoices where ProjectID = 913) 
) AS t2

when I try to use select query. I encounter an error. Is select statement possible on PIVOT? Thanks.

check this out you will get some idea http://stackoverflow.com/questions/32502964/how-do-i-display-column-data-in-rows-grouping-by-column-values-using-sql-server/32509395#32509395 — Chandrasekar Kesavan, Sep 23 '15 at 05:11
You have to use dynamic query. Collect all the column and COALESCE it then apply the those values in dynamic query — Chandrasekar Kesavan, Sep 23 '15 at 05:14

Using "select statement" instead of "dynamic column" on PIVOT

0 Answers0