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?

Chris Pickford
– Chris Pickford

2016-08-03 08:44:39 +00:00
Commented Aug 3, 2016 at 8:44 — Chris Pickford
– Chris Pickford, Commented Aug 3, 2016 at 8:44
What does the pivot have to do with the select @data?

Jonny
– Jonny

2016-08-03 08:49:39 +00:00
Commented Aug 3, 2016 at 8:49 — Jonny
– Jonny, Commented Aug 3, 2016 at 8:49
@jonny ... it should get all the paycode names !

Bishoy Ezzat
– Bishoy Ezzat

2016-08-03 08:51:16 +00:00
Commented Aug 3, 2016 at 8:51 — Bishoy Ezzat
– Bishoy Ezzat, Commented Aug 3, 2016 at 8:51
@ChrisPickford I saw it and it is not clear for me :/

Bishoy Ezzat
– Bishoy Ezzat

2016-08-03 08:53:06 +00:00
Commented Aug 3, 2016 at 8:53 — Bishoy Ezzat
– Bishoy Ezzat, Commented Aug 3, 2016 at 8:53

Jonny · Accepted Answer · 2016-08-03 08:55:00Z

1

To get all the paycodes in @data use

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

Then remove the last comma

answered Aug 3, 2016 at 8:55

Jonny

1,21110 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bishoy Ezzat Over a year ago

(Y) it worked ... but no result retrieved .. do you have any idea why didn't the query retrieve the result sets ??!

Bishoy Ezzat · Accepted Answer · 2016-08-03 11:44:29Z

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)

Jonny · Accepted Answer · 2016-08-04 12:36:50Z

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 4, 2016 at 12:36

answered Aug 3, 2016 at 9:19

Jonny

1,21110 silver badges16 bronze badges

3 Comments

Jonny Over a year ago

I used temptables to try it out. Substitute the names to your tables

Bishoy Ezzat Over a year ago

check my answer ... Thanks 4 help :)

Jonny Over a year ago

Please mark as the solution if it helped. I corrected the typo too.

Collectives™ on Stack Overflow

how can I use Pivot using a variable in SQL?

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related