CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
Jav Hens
Jav Hens

Reputation: 73

change column names in a dynamic pivot table result

I have a tsql dynamic pivot table query that works fine although I´m not happy with the column names in the final output.

To make it work, the user has to select up to 20 fruit names from a list of 200 fruit names. Then I build the pivot table so I end up with different column names everytime the select is run. For example: First time the column names are: apple, orange and pear Second time is: .orange, banana, kiwi and apple

My question is: Is there a posibility to have static names, like for example: name of the first column always "col_1", second column "col_2" etc?

The select statement is as follows: 

DECLARE @idList varchar(800) 
DECLARE @sql nvarchar(max)

SELECT @idList = coalesce(@idList + ', ', '') + '['+ltrim(rtrim(id_producto)) +']'
from gestor_val_pos
group by id_producto order by id_producto

SELECT @sql = 'select * from #correlaciones pivot (max (correl) 
for codigo2   in (' + @IDlist + ')) AS pvt order by codigo1;'

exec sp_executeSQL @sql

Upvotes: 2

Views: 2066

Answers (2)

eftpotrm
eftpotrm

Reputation: 2271

Yes, but it'll make your query significantly more complex.

You'll need to return a list of the possible column names generated from @IDList and convert that into a SELECT clause more sophisticated than your current SELECT *.

When you've got that, use some SQL string splitting code to convert the @IDList into a table of items with a position parameter. Append AS <whatever> onto the end of any you want and use the FOR XML PATH trick to flatten it back, and you've got a SELECT clause that'll do what you want. But, as I said, your code is now significantly more complicated.

As an aside - I really hope that @idList is either completely impossible for any user input to ever reach or hugely simplified from your real code for this demonstration. Otherwise you've got a big SQL injection hole right there.

Upvotes: 1

JamieD77
JamieD77

Reputation: 13949

sure.. just created a new variable to hold the column aliases and Row_Number to get the column number.

DECLARE @idList varchar(800) 
DECLARE @idListAlias varchar(800) 
DECLARE @sql nvarchar(max)

SELECT 
    @idList = coalesce(@idList + ', ', '') + '['+ltrim(rtrim(id_producto)) +']',
    @idListAlias = coalesce(@idListAlias + ', ', '') + '['+ltrim(rtrim(id_producto)) +'] as col_' + CONVERT(VARCHAR(10), ROW_NUMBER() OVER(ORDER BY id_producto))
from gestor_val_pos
group by id_producto order by id_producto

SELECT @sql = 'select ' + @idListAlias + ' from #correlaciones pivot (max (correl) 
for codigo2   in (' + @IDlist + ')) AS pvt order by codigo1;'

exec sp_executeSQL @sql

Upvotes: 2

Related Questions