Is it possible to do multiple pivots without explicitly naming each column in SQL Server?

Question

My challenge are these:

How do this without explicitly rename the pivoted columns "in ( ... )"?
Integrate that solution for multiple pivots

This is the code I have so far:

PIVOT 
    (SUM([Revenue])
       FOR [Year Month] IN ([201710], [201711]) 
    ) AS Pivot1

PIVOT 
    (SUM([Gross Profit])
       FOR [Year Month] IN ([201710], [201711])  
    ) AS Pivot2

If you do it via aggregates in the select and forsake the often forsaken pivot keyword you can leave the column names null — SCFi, Apr 20 '18 at 18:55
use dynamic sql as answer below describes - here's a nice article that may help https://thesqlserverdeveloper.blogspot.com/2018/02/performing-dynamic-pivot.html?view=magazine — Daniel Marcus, Apr 20 '18 at 19:12
Thanks @DanielMarcus. It's seeing actual code examples helped — Vinh Ton, Apr 30 '18 at 18:20
Great - Feel free to browse through the rest of my blog for other interesting SQL Server topics/problems complete with actual code and sample data — Daniel Marcus, Apr 30 '18 at 18:27

score 2 · Accepted Answer · answered Apr 20 '18 at 19:09

The SQL language has a very firm requirement that you know the number and types of each column up front. Query optimizers/db engines need this in order to build efficient execution plans and evaluate permissions on columns used in a query. Even using SELECT * meets this requirement, because the number of columns in any given table or view used in the rest of the query is fixed and known (at least for the life of that query).

What you are asking for requires the number of columns in the results be determined by the data, which isn't known until after an execution plan is created. That's not allowed. The only way you can accomplish this is via dynamic SQL, where you have three steps. First run a SELECT to get the columns you'll need. Then use that data to build a new query string on the fly. Finally, execute the query string you just built and return the data to the user.

Ended up doing a combination of cross apply pivot and dynamic sql for future reference — Vinh Ton, Apr 30 '18 at 18:21
https://stackoverflow.com/questions/15274305/is-it-possible-to-have-multiple-pivots-using-the-same-pivot-column-using-sql-ser — Vinh Ton, Apr 30 '18 at 18:22

score 0 · Answer 2 · answered Apr 20 '18 at 19:19

Normally without dynamic SQL you don't have many options.You need to use aliases in SELECT col as alias.

In Oracle you could achieve better results using:

SELECT *
FROM  source
PIVOT (SUM(Revenue) AS Revenue, SUM(Profit) AS Profit 
       FOR (YearMonth) IN (201801, 201802)) s;

DBFiddle Demo

score 0 · Answer 3 · answered Apr 20 '18 at 19:43

Do you want get following return? I don't understand your Goal, why the 201802 Revenue of B return in the Revenue 201801.

     if object_id('tempdb..#t') is not null drop table #t
    go
    create table #t(Customer varchar(100),[Date] varchar(100),Revenue int,Profit int)
    insert into #t(Customer,[Date],Revenue,Profit)
    select 'a','201801',100,1 union all
    select 'a','201801',10,11 union all
    select 'b','201802',200,20

    declare @sql nvarchar(max),@cols nvarchar(max)
    select @cols=isnull(@cols+',','')+quotename('Revenue '+[Date])+','+quotename('Profit '+[Date]) from #t group by [date]

    set @sql='
    select * from (
    select customer,colname,colvalue 
    from #t
    cross apply(values(''Revenue ''+[Date],Revenue),(''Profit ''+[Date],Profit)) c(colName,ColValue)
    ) as t pivot (sum(colvalue) for colname in ('+@cols+')) p'
    exec(@sql)

+----------+----------------+---------------+----------------+---------------+
| customer | Revenue 201801 | Profit 201801 | Revenue 201802 | Profit 201802 |
+----------+----------------+---------------+----------------+---------------+
| a        | 110            | 12            | NULL           | NULL          |
| b        | NULL           | NULL          | 200            | 20            |
+----------+----------------+---------------+----------------+---------------+

Is it possible to do multiple pivots without explicitly naming each column in SQL Server?

3 Answers3