Transpose or pivot SQL @Table

Question

I have a procedure that uses the dynamic input parameter @Instring VarChar = '1,2,3,4'. This procedure populates @RetTable like this:

myDates     myReturns   ID
2012-05-02  0.020       1
2012-05-03  -0.017      1
2012-05-04  -0.026      1
2012-05-02  0.009       2
2012-05-03  0.004       2
2012-05-04  -0.003      2
2012-05-02  -0.003      3
2012-05-03  -0.005      3
2012-05-04  -0.003      3
2012-05-02  0.004       4
2012-05-03  0.010       4
2012-05-04  -0.021      4

I then want to pivot or transpose @RetTable by the ID column to look like this:

myDates      1        2       3       4
2012-05-02  0.020   0.009   -0.003  0.004
2012-05-03  -0.017  0.004   -0.005  0.010
2012-05-04  -0.026  -0.003  -0.003  -0.021

this is the code I have thus far:

Select * From @ReturnsTable
Pivot(Max(myReturns) For [ID] In ([1],[2],[3],[4])) As myPTable

This part of code:

([1],[2],[3],[4]))

I want to replace with/to reference @InString in order to have a dynamic procedure. I have tried various dynamic sql posts on SO but run into issues with every other attempt. Any ideas?

EDIT:

In answer to @bluefeet. What I have tried;

1)

Execute
('Select * From @TempTable
Pivot(Max(myReturns) For ID In (' + @InString + ')) As myPTable')

Result: error must declare @TempTable - table out of scope

2) Then changed @TempTable to #TempTable; Prints as:

Select * From #TempTable
Pivot(Max(myReturns) For ID In (1,2,3)) As myPTable

When execute that, I get error: Incorrect syntax near '1'

Which leads me to think the ID's should be delimited as [1],[2],[3]..

Earlier I have also tried executing with the normal execute, but also the Exec sp_executesql

As far as your second attempt that returns an incorrect syntax - since your columns are numeric, then you need to surround each column with a square bracket (i.e., `[1], [2], [3]`) — Taryn, Feb 24 '14 at 18:09

score 2 · Accepted Answer · edited May 23 '17 at 11:57

2

If you try to do this

declare @sql nvarchar(1000) = 'Select * From @ReturnsTable Pivot(Max(myReturns) For [ID] In (' 
     + @InString+ ') As myPTable'

exec sp_executesql @sql

It won't work

This is because your @returnsTable is in the scope of your code, and your dynamic SQL will be in a different scope. If you want to pass a table variable to the dynamic SQL, you need to make it a defined type, and pass it as a parameter.

See using Table variable with sp_executesql

If you can use a temporary #table instead of a table variable, dynamic SQL will work.

edited May 23 '17 at 11:57

Community

1
1

answered Feb 24 '14 at 15:14

podiluska

50,950
7
98
104

With using the #Table, I also wrote a small function which delimits everything between brackets of @Instring, then the dynamic SQL works – Allstar Feb 26 '14 at 18:30

Siddique Mahsud · Answer 2 · 2014-02-24T16:34:49.867

DECLARE  @InString varchar(50)='1,2,3,4'

USE Print statement first so you confirm that you are executing correct query dynamically

PRINT 
'Select * From @ReturnsTable 
Pivot(
Max(myReturns) 
For [ID] In (' + @InString+ ') As myPTable'

Output

Select * From @ReturnsTable 
Pivot(
Max(myReturns) 
For [ID] In (1,2,3,4) As myPTable

When you sure the print is correct then execute it by using EXECUTE

EXECUTE(
'Select * From @ReturnsTable 
Pivot(
Max(myReturns) 
For [ID] In (' + @InString+ ') As myPTable'
)

Transpose or pivot SQL @Table

2 Answers2