The World of Dynamic Pivoting – in SQL Server

Times are changing, databases are getting more sophisticated and there are in-built functions that previous DBAs had to solve for themselves.

One of these was the dynamic pivot.

No doubt, there are still some older versions of RDBMs users out there writing dynamic pivots.

Consider the scenario, you are gathering information as an input from a large number of people.

Could be preferences for study, preferences for gender, insurance opt-ins, menu selections.

When you ask what someone wants, they answer it is recorded.

Problem is: it looks something like this:

Not very helpful?

Right?

We can get the information we want if we know what the questions are. Cool.

WITH QUERY AS (

SELECT PERSON, QUESTION, ANSWER

FROM PIVOT_EXEMPLAR

)

SELECT *

FROM QUERY

PIVOT (MAX(ANSWER) FOR QUESTION IN ([A], [B])

) as x

What if we don't?

We could just stick a SELECT DISTINCT inside the pivot statement.

Couldn't we?

Sometimes the answer is Yes.

If so, that is a better option. If not, read on.

We need to write a query that can print the pivot statement that we need.

We are going to take advantage of the string_agg group by condition in SQL Server to roll up the strings in the pivoted column, in this case, the column titled question.

WITH A AS (

SELECT DISTINCT QUESTION

FROM PIVOT_EXEMPLAR

)

,

A2 AS (

SELECT STRING_AGG(CONCAT('[',QUESTION,']'),',') as string_2

FROM A

)

SELECT *

FROM A2

This gives us a very tidy little correlated sub-query section.

We can combine all of this together to yield our dynamic pivot:

WITH A AS (

SELECT DISTINCT QUESTION

FROM PIVOT_EXEMPLAR

)

,

A2 AS (

SELECT STRING_AGG(CONCAT('[',QUESTION,']'),',') as string_2

FROM A

)

,

B AS (

SELECT 'WITH QUERY AS (

SELECT PERSON, QUESTION, ANSWER

FROM PIVOT_EXEMPLAR

)

SELECT *

FROM QUERY

PIVOT (MAX(ANSWER) FOR QUESTION IN(' as string_1

)

SELECT CONCAT(STRING_1, ' ', STRING_2,')) as x')

FROM A2 CROSS JOIN B

When executed we get:

WITH QUERY AS ( SELECT PERSON, QUESTION, ANSWER  FROM PIVOT_EXEMPLAR )  SELECT *  FROM QUERY  PIVOT (MAX(ANSWER) FOR QUESTION IN( [A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L])) as x

Running this we get:

Which is the answer to both person's questions.

We can roll that statement into a variable and execute at run-time if we feel like it.

Another useful thing to note is that if someone accidentally deletes some questions the query will not fail to compile.

Consider this:

WITH A AS (

SELECT DISTINCT QUESTION

FROM PIVOT_EXEMPLAR

WHERE QUESTION NOT IN ('A', 'B', 'F', 'H')

)

,

A2 AS (

SELECT STRING_AGG(CONCAT('[',QUESTION,']'),',') as string_2

FROM A

)

,

B AS (

SELECT 'WITH QUERY AS (

SELECT PERSON, QUESTION, ANSWER

FROM PIVOT_EXEMPLAR

)

SELECT *

FROM QUERY

PIVOT (MAX(ANSWER) FOR QUESTION IN(' as string_1

)

SELECT CONCAT(STRING_1, ' ', STRING_2,')) as x')

FROM A2 CROSS JOIN B

Comment with the answer below.