how can I use Pivot using a variable in SQL?

Question

I have paycode numbers and Names from table Paycodes and I have Amount in monthlyTransaction. as follows:

Paycodes
Code     Name
1        Basic Salary
2        Variable Deduction Amount
3        Fixed/Var Insurance PayCode

MonthlyTransaction
Code     Amount
1        3000
2        10000
1        130000
1        150000
3        120000

I want it to be like this using pivot

Basic Salary    Variable Deduction Amount  Fixed/Var Insurance PayCode
31000           10000                      120000

I want to use pivot to sum the Amount of each Paycode and I used this:-

DECLARE @data AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)


--  DECLARE @data TABLE
--(
--   PaycodeName NVARCHAR(max)
--)
--INSERT INTO @data
--        ( PaycodeName )
--select dbo.Paycode.PayCodeName FROM dbo.Paycode 




select @data = Paycode.PayCodeName FROM Paycode


set @query = 'SELECT * FROM (
    SELECT Paycode.Name , MonthlyTransaction.Amount
    From MonthlyTransaction
    LEFT JOIN dbo.Paycode ON Paycode.code = MonthlyTransaction.Paycode
    ) AS s
    PIVOT
    (
        SUM(Amount)
        FOR Paycode.Name IN ('+@data+')
    ) AS pvt '

EXECUTE Sp_executesql @query

When I print @data it retrieve the last paycode only ! Can anyone help ?

Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Chris Pickford, Aug 03 '16 at 08:44

score 1 · Answer 1 · answered Aug 03 '16 at 08:55

1

To get all the paycodes in @data use

SELECT @data = @data + Paycode.PayCodeName + ', '
FROM Paycode

Then remove the last comma

answered Aug 03 '16 at 08:55

Jonny

1,037
7
15

(Y) it worked ... but no result retrieved .. do you have any idea why didn't the query retrieve the result sets ??! – Bishoy Ezzat Aug 03 '16 at 08:58

Jonny · Answer 2 · 2016-08-04T12:36:50.657

0

This should do the trick

 SELECT 1 Code, 'Basic Salary' Name
 INTO #Paycode
 UNION
 SELECT 2 Code, 'Variable Deduction Amount' Name
 UNION
 SELECT 3 Code, 'Fixed/Var Insurance PayCode' Name

 SELECT 1 Code, 3000 Amount
 INTO #MonthTrans
 UNION
 SELECT 2 Code, 10000 Amount
 UNION
 SELECT 1 Code, 130000 Amount
 UNION
 SELECT 1 Code, 150000 Amount
 UNION
 SELECT 3 Code, 120000 Amount

 DECLARE @data AS NVARCHAR(MAX)

 SELECT @data = ISNULL(@data,'') + '[' +CAST(Code AS varchar) + '], '
 FROM #Paycode

 SELECT @data=SUBSTRING(@data, 0, LEN(@data))


 DECLARE @query  AS NVARCHAR(MAX)

 SELECT @query = 'SELECT * 
 FROM
    #MonthTrans M
 PIVOT ( 
    SUM(Amount) FOR Code IN (' + @data + ')
 ) pvt'


 EXECUTE Sp_executesql @query 


 drop table #paycode
 drop table #MonthTrans

edited Aug 04 '16 at 12:36

answered Aug 03 '16 at 09:19

Jonny

1,037
7
15

I used temptables to try it out. Substitute the names to your tables – Jonny Aug 03 '16 at 10:28
check my answer ... Thanks 4 help :) – Bishoy Ezzat Aug 03 '16 at 13:05
Please mark as the solution if it helped. I corrected the typo too. – Jonny Aug 04 '16 at 12:37

score 0 · Accepted Answer · answered Aug 03 '16 at 11:44

the answer is :

DECLARE @codeNameCol NVARCHAR(max)

SELECT   @codeNameCol= COALESCE(@codeNameCol + ',','') + QUOTENAME(RTRIM(LTRIM(PayCodeName)))
FROM (SELECT DISTINCT PayCodeName FROM Paycode) AS codeNameCol

DECLARE @querys NVARCHAR(max)

Set @querys='
SELECT  *
FROM    ( SELECT    dbo.EmpAssignment.EmployeeId ,
                PayCodeName ,
                Amount
      FROM      dbo.MonthlyTransaction
                LEFT JOIN dbo.Paycode ON Paycode.code = MonthlyTransaction.Paycode
                LEFT JOIN dbo.EmpAssignment ON EmpAssignment.EmpId = MonthlyTransaction.EmpId
                LEFT JOIN dbo.PayrollGroup ON PayrollGroup.PayrollGroup = EmpAssignment.PayrollGroup
    ) DataTable PIVOT
( SUM(Amount) FOR PayCodeName IN ( '+@codeNameCol+' ) ) PivotTable;'

EXEC (@querys)

how can I use Pivot using a variable in SQL?

3 Answers3