This is a follow-up to this FAQ:

When using an array type variable to build the IN list for a PIVOT clause, it would be handy to specify aliases for the generated columns.

For IN with a constant expression, I can add the alias with an AS clause, such as

   ...
   FOR DepartmentID IN
      (100 as "Dept. 100", 200 as "Dept. 200", 300 as "Dept. 300", 400 as "Dept. 400")

and get columns like "Dept. 100_TotalSalary" in the result set.

How do I do that when using "IN variable"?

I've tried to use an array of aliased values, such as the following, but that fails because the whole field value (e.g. '100 as Dept100') seems to used as pivot value, returning no matches. (Note that I had to cast DepartmentID to varchar in the pivot-source-table, probably due to the same issue...)

CREATE VARIABLE @varAliasedDepartments varchar(20) ARRAY;
SET @varAliasedDepartments =
   (SELECT ARRAY_AGG(DISTINCT DepartmentID || ' as Dept' || DepartmentID ORDER BY DepartmentID) FROM Employees);
SELECT * 
FROM ( SELECT cast(DepartmentID as varchar) as DepartmentID, State, Salary 
       FROM   Employees
       WHERE State IN ( 'OR', 'CA', 'AZ', 'UT' )
     ) MyPivotSourceData
   PIVOT ( 
      SUM( Salary ) TotalSalary  
      FOR DepartmentID IN @varAliasedDepartments
   ) MyPivotedData
ORDER BY State;

asked 30 Nov '15, 11:31

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

edited 30 Nov '15, 11:32

So there's currently no solution for that topic?

(Other than the probable workaround of using something like sa_describe_query() to get to know the default names of the pivot columns, and then use EXECUTE IMMEDIATE to build a derived query around the whole query to replace the default names of the pivot columns with aliased ones...)

(11 Mar '16, 05:47) Volker Barth
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×80
×9

question asked: 30 Nov '15, 11:31

question was seen: 476 times

last updated: 11 Mar '16, 05:52