CODEWITHSUNDEEP
sql-serversql-server-2008pivotpivot-tabletemp-tables
Rupesh S
Rupesh S

Reputation: 125

How to insert data into another table retrieved from Pivot select statement and containing dynamic columns?

Here's my query ,

DECLARE @BranchAsColumn nvarchar(max) -- it may contain branchesName sepereated by comma(,) using as dynamic column, and it may vary e.g. #ABC,#LMN,#XYZ

DECLARE @Branches nvarchar(max) -- it may contain branchesName sepereated by comma(,) using for filter condition, and it may vary e.g. ABC,LMN,XYZ

EXEC('Select CompanyID,Company,Category,SubCategory,Months,'+@BranchAsColumn+'                  
                    From
                        (   Select                                                      
                                BranchID,
                                Branch,                             
                                CompanyID,  
                                Company,    
                                Months,
                                Department,                             
                                Amount                              
                            From #Data
                        )P
                        PIVOT
                        (
                            SUM(Amount)
                            For Branch in ('+@Branches +')
                        ) As Pivottable')

What ever result I will get here I just wanted to insert retrieved data into Another TABLE, so please anyone here help me to get the perfect solution.

Upvotes: 1

Views: 1999

Answers (1)

Zein Makki
Zein Makki

Reputation: 30052

Try this out: 

EXEC('Select CompanyID,Company,Category,SubCategory,Months,'+@BranchAsColumn+'
        INTO ##MyTable
        From ( Select
        BranchID, Branch,
        CompanyID,
        Company,
        Months, Department,
        Amount
        From #Data )P PIVOT ( SUM(Amount) For Branch in ('+@Branches +') ) As Pivottable')

 -- test results
SELECT * FROM ##MyTable

Upvotes: 2

Related Questions