Can I use PIVOT without the brackets?

Question

PIVOT 
(
    count(DueCount) FOR dueLibraries.s_folder IN ([Assembly Report-TUL],[Balance-TUL],[BOM-TUL],[Hydrostatic-TUL],[Inspection-TUL],[IOM Manual-TUL],[MTR-TUL],[NDT-TUL],[Performance-TUL],[Inputs - TUL],[Transmitted])
) as MonthlyTally

I rather just have this:

PIVOT 
(
    count(DueCount) FOR dueLibraries.s_folder IN (select * from dueLibraries)
) as MonthlyTally

Is there a way to do that?

The brackets are needed because of the spaces and dashes in your names. — Joe Stefanelli, Aug 10 '11 at 20:44
Ugh. Now I'm gonna need to do string manipulation in SQL.... Mercy. — dotnetN00b, Aug 10 '11 at 20:49
In any event, the syntax does not allow you to replace the explicit list of columns in the PIVOT clause, with a mask (to say nothing about using SELECT there). If the number of values/columns is undefined then you probably want to do a [dynamic PIVOT](http://stackoverflow.com/questions/1122117/sql-dynamic-pivot-how-to-order-columns). — Andriy M, Aug 11 '11 at 00:10

score 4 · Accepted Answer · edited May 23 '17 at 10:34

Your question is two-fold, as it seems.

First of all, the identifiers in your first snippet's IN list are delimited identifiers. They have to be delimited with square brackets because they do not obey the rules for regular identifiers in Transact-SQL (particularly, because they include spaces and hyphens).

The second part of your question is about replacing the explicit list of columns with something like a mask, to make the list dynamic. Now, there's no available syntax for that, and your only option seems to be a dynamic query with a PIVOT clause. Here's one example of how it can be implemented.

Can I use PIVOT without the brackets?

1 Answers1