I have a dynamic pivot query which transposes rows to columns. At the moment I have all the data in one table but can separate it if two separate tables is easier.
I only need some the column ChargeNames pivoted as such (with the related charges included) Is it possible to only pivot part of the table and join the new Pivot table onto the source table?
Or could I create a new table with the required data and pivot it, and join it up to another table?
Apologies if it sounds somewhat confusing
Source table with all data
Pivot table (column for pivot is Chargenames)
The Code - Declaring and populating table
--Create temporary table CREATE TABLE ##tempGetPaymentAnalysisByMember ( MemberID NVARCHAR(MAX), FirstName NVARCHAR(MAX), Surname NVARCHAR(MAX), CategoryName NVARCHAR(MAX), InvoiceID INT, ChargeNames NVARCHAR(MAX), ChargeAmount INT, TotalAmount INT ) --Populate the table INSERT INTO ##tempGetPaymentAnalysisByMember(MemberID, FirstName, Surname, CategoryName, InvoiceID, ChargeNames, ChargeAmount, TotalAmount) SELECT Member.ID, Member.FirstName, Member.Surname, Category.Name, Invoice.ID, ChargeType.Name, Charge.Amount, Invoice.Amount FROM dbo.Member, dbo.Category, dbo.Invoice, dbo.ChargeType, dbo.Charge WHERE Category.ID = Member.CategoryID AND Member.ID = Invoice.MemberID AND Charge.ChargeTypeID = ChargeType.ID AND Charge.InvoiceID = Invoice.ID --Test select to check if data is correct SELECT * FROM ##tempGetPaymentAnalysisByMember
The Pivot Query
SELECT @ColumnName = ISNULL(@ColumnName + ',','') +QUOTENAME(ChargeNames) FROM (SELECT DISTINCT ChargeNames FROM ##tempGetPaymentAnalysisByMember) AS ChargeName SET @DynamicPivotQuery = 'SELECT ' + @ColumnName + ' FROM ##tempGetPaymentAnalysisByMember PIVOT(MIN(ChargeAmount) FOR ChargeNames IN (' + @ColumnName + ')) AS PivotTable' EXEC sp_executesql @DynamicPivotQuery