Indefinite amount of column in Pivot

Question

I have created a function with PIVOT that turns the string into table which is the output I want. What I want is I don't need to declare the columns one by one.

Here's the code:

SELECT 
Name1, Name2, Name3
FROM
(
  SELECT 
  LEFT(CA.val,CHARINDEX('=',CA.val)-1) ColumnName,
  SUBSTRING(CA.val,CHARINDEX('=',CA.val)+1,100) Value
  FROM Z_SampleTable
  CROSS APPLY dbo.SplitSample(ProcessBody,'|') CA
) PD
PIVOT
(
  MAX(Value)
  FOR ColumnName IN (Name1, Name2, Name3)
) 
AS PT

Is there a way that doesn't need to declare each column? For example, I have a table that contains 100 columns, is there a way not to declare those 100 columns?

Because any *particular* SQL query has a fixed "shape" - the number, **names** and types of the columns are rigidly set. So if you want to vary the number/names of columns in a result set, you inevitably have to write a new query at runtime - hence, dynamic sql. — Damien_The_Unbeliever, Sep 22 '15 at 06:43
@Damien_The_Unbeliever is spot on I think. [I've done a fair share of research into getting a SQL(-related) solution for this](http://softwarerecs.stackexchange.com/q/2846/1008) if you're interested (SSRS is in fact decent for some, smaller, cases). Another option is of course polyglot persistance and using your NoSQL side for this job. But for adhoc SQL queries, pivoting with unknown columns requires dynamic SQL for sure. — Jeroen, Sep 22 '15 at 06:51

score 1 · Answer 1 · edited May 23 '17 at 12:32

With SQL based solutions you're required to use a dynamic SQL solution. Build your SELECT query dynamically, crafting the PIVOT bit yourself (beware of injection attacks!), and then using EXEC(@dynamicSql) to get the results. Something like this answer to another question.

I've done a fair share of research into this, and if you're willing to go outside the realm of plain SQL queries you'll start to get more options. SSRS for example is pretty good at dynamically pivotting normalized datasets as long as they're small. For bigger scenarios while staying with SQL you'll need bigger guns, all requiring being "smart" about your query's (i.e. building them dynamically).

Finally, for bigger scenario's you can use polyglot persistance, and create a replica of your data in a schemaless NoSQL solution. These typically allow you to do this with ease.

But the bottom line, for ad hoc SQL queries: you'll have to build the FOR ColName IN (...) bit dynamically and then EXEC(@myQuery).

Indefinite amount of column in Pivot

1 Answers1