Dynamic PIVOT

Sample for Dynamic PIVOT Query:

Some time we need complex cross tab result for report, that time we can use the PIVOT Query, for example we have 3 Tables and we have data like this :


IF OBJECT_ID('tempdb..#customer') IS NOT NULL
DROP TABLE #customer

CREATE TABLE #customer (
CustomerId INT,
CustomerName VARCHAR(50)
)


INSERT #customer
SELECT 1, 'Ram'
INSERT #customer
SELECT 2, 'Hari'
INSERT #customer
SELECT 3, 'Depesh'
INSERT #customer
SELECT 4, 'Sailesh'

--Select * from #customer



————————————————————————–

IF OBJECT_ID('tempdb..#LoanType') IS NOT NULL
DROP TABLE #LoanType

CREATE TABLE #LoanType (
LoanId INT,
LoanName VARCHAR(50)
)


INSERT #LoanType
SELECT 1, 'Loan-1'
INSERT #LoanType
SELECT 2, 'Loan-2'
INSERT #LoanType
SELECT 3, 'Loan-3'
INSERT #LoanType
SELECT 4, 'Loan-4'
INSERT #LoanType
SELECT 5, 'Loan-5'
INSERT #LoanType
SELECT 6, 'Loan-6'

--Select * from #LoanType


——————————————————————————

IF OBJECT_ID('tempdb..#LoanCustomer') IS NOT NULL
DROP TABLE #LoanCustomer


CREATE TABLE #LoanCustomer (
CustomerId INT,
LoanId INT,
Amount MONEY
)


INSERT #LoanCustomer
SELECT 1, 1, 500
INSERT #LoanCustomer
SELECT 2, 1, 5300
INSERT #LoanCustomer
SELECT 3, 3, 1500
INSERT #LoanCustomer
SELECT 2, 1, 2500
INSERT #LoanCustomer
SELECT 1, 6, 5500
INSERT #LoanCustomer
SELECT 2, 4, 1100
INSERT #LoanCustomer
SELECT 2, 3, 3200
INSERT #LoanCustomer
SELECT 2, 3, 3200

--Select * from #LoanCustomer


——————————————————————————–

Combine Result of three table from PIVOT Query:


DECLARE
@sql VARCHAR(MAX)
,@LoanType VARCHAR(MAX)


SELECT
@LoanType = ISNULL(@LoanType + ', ', '') + '[' + LoanName + ']'
FROM (
SELECT
DISTINCT
l.LoanName
FROM #LoanCustomer lc
INNER JOIN #LoanType l ON lc.LoanId = l.LoanId
) x


SET @sql = '
SELECT
CustomerName
,' + @LoanType +
Sample for Dynamic PIVOT


FROM (
SELECT
c.CustomerName,
l.LoanName,
lc.Amount
FROM #LoanCustomer lc
INNER JOIN #customer c ON lc.CustomerId = c.CustomerId
INNER JOIN #LoanType l ON l.LoanId = lc.LoanId
) dataList
PIVOT
(
SUM(Amount)
FOR LoanName IN(' + @LoanType+ ')
) X '


EXEC (@sql)


 

This Script is Prepared by Netra Prasad Acharya (MSSQL Developer)

Advertisements