-3

Are there any ways to achieve dynamic pivoting tables in SQL Server through an elegant function? Pivoting tables is a basic utility which theoretically might be done in one line of code. All we need is to define parameters: column variable to pivot (change its values into column names), variable to aggregate, aggregate function, and table on which we perform the pivoting.

I look for alternatives to the syntax Microsoft proposes for pivoting tables in SQL Server in the 2019 year.

A practical alternative I found here: https://stackoverflow.com/a/45065584/1903793

All complex code is wrapped with a stored procedure. In this elegant one-liner, we define all parameters which are needed for pivoting. 

exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','sum'

But the caveat of that solution is that it is a stored procedure so it requires a static table as input and it outputs also a static table. Would be better if it might be a sort of TVF from which we can select.

Update after comments. Until I started using SQL Server I did not even know that pivoting might be static. The need is to do dynamic pivoting. Lots of other applications do pivoting tables on the fly (Excel, Power BI, R) without constraint that column names must be known apriori. It might be a problem in 2005 to realize that pivoting might be done as a function with parameters. But, hey, we are going to the future, and it is ridiculous that we are heading next decades with the complex constrained syntax for this basic table transformation utility.

Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
  • An alternative because you think the syntax is wonky is not at all the same as actually *needing* a dynamic pivot. In any case, under no circumstance would it be possible to package something like this as a TVF, because T-SQL abhors dynamic result sets. It's always a safe bet that if you need output where the shape depends on the input, the only thing that can even do it is a stored procedure, and even then probably not comfortably. (Also, the syntax is "only" 14 years old, having been introduced with 2005, although the dev involved may or may not have been named Moses.) – Jeroen Mostert May 24 '19 at 13:54
  • 1
    A TVF would be better, but they don't support dynamic SQL. – John Cappelletti May 24 '19 at 13:55
  • I'm honest, I never remember the syntax for `PIVOT` but that's because I only use cross tabs as they perform better and are more flexible on what they can do. – Luis Cazares May 24 '19 at 14:19
  • 2
    It's not clear what kind of answer you're looking for. Evidently the current ones aren't doing it for you if you're offering a bounty, but realistically, what do you want to get out of this question? Validation of your opinion? A promise from Microsoft? A short, accurate answer to "is there a simple way to do dynamic pivoting in T-SQL" would be "no". There are other questions addressing the problem itself, and you already linked to one of them. What's more? – Jeroen Mostert May 27 '19 at 10:11
  • @JeroenMostert I know that the short answer is "no". Pivoting is a common wide-spread need, so I hope that someone will share a workaround. I hope that others will share their trials in dealing with the problem. I could think of calling R or Python script from SQL Server or designing CLR function for that. If I knew the answer, I would not post the question. – Przemyslaw Remin May 27 '19 at 11:29
  • OK -- just trying to confirm that this is functionally a duplicate of the linked question, you just want newer/different answers. FWIW, I don't think there's a workaround. It's nothing to do with pivoting specifically -- T-SQL's need for a static result set is a fundamental part of the implementation. External languages/scripts can't really do better than dynamic SQL in this regard. – Jeroen Mostert May 27 '19 at 11:56
  • 2
    @PrzemyslawRemin, you've already had your answer on this question. The only solution or "workaround" to dynamic pivot, is to execute a sproc which employs dynamic SQL. There is no alternative. A CLR function will not help because that must also define its output columns apriori - any "function" in SQL whose results may potentially be piped into a further query, must have a static table structure. – Steve May 27 '19 at 18:56

4 Answers4

3

But the caveat is that it is a stored procedure so it requires a static table as input and it outputs also a static table.

I don't understand this. The table name is one of the arguments, so this does not require a "static table".

More importantly, the stored procedure cannot be written as a (reasonable) user-defined function, because it requires dynamic SQL. And user-functions don't support dynamic SQL.

You also need to realize that anything in the FROM clause requires that the columns and types of columns be known during the compilation phase of the query. This precludes the use of strings to specify column names, because the string could be a parameter.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Which standards committee are you seeking to blame? As far as I can tell `PIVOT` is not part of any version of ANSI SQL, and I wouldn't go so far as to call the folks who think up new things to put in T-SQL a committee (and if they are, what they're responsible for isn't a standard...) – Jeroen Mostert May 24 '19 at 14:25
  • @JeroenMostert . . . I think I have a short-hand that if Oracle and SQL Server both support a particular function, then it must be standard. That fails in this case. Thank you for keeping me honest. – Gordon Linoff May 25 '19 at 01:50
2

Excel doesn't actually do dynamic pivot fully - you still have to specify the ordering of the pivoted columns or the aliasing of column names using the GUI to finesse the final presentation (although in some limited cases it doesn't matter).

You also (so far as I recall) cannot in Excel specify columns whose headers don't exist at all in the source data - in some cases you have to include dummy rows in the source, just to force a particular column to be present in the pivot output (albeit the values in such a column will all be zero or empty, but it is often important in final reports to say so explicitly and/or maintain the visual structure of the pivot).

Obviously, SQL requires all this to be done up front in the code itself.

I agree that the SQL syntax is the pits, but honestly, unless the column ordering is immaterial, the column names don't require aliasing, and no particular column is required to be present (threefold criteria that aren't met in the vast majority of cases for which a dynamic pivot would actually be useful), then I don't see how else the problem could be dealt with.

Hacks using a stored procedure to invoke dynamic SQL, in which the programmer has flexibility to determine all aspects of the behaviour and match it to the requirements of the particular case, is probably the best balance that can be achieved.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Steve
  • 950
  • 7
  • 11
2

SQL Server is a relational database management system. The concept of a relation is at the foundation of its design. All relational database management systems are designed under assumption that the schema of the database is stable. In other words, all RDBMSs assume that the list of tables and their columns are static. They are designed to work efficiently when data is structured in this way.

It looks like you have some expectations from a tool that it wasn't designed for.

I don't have experience with non-relational DBMSs. Nowadays there are tons of them and some may be a better fit for your tasks than RDBMS.

With RDBMS there is often a separation of data and its representation to the end user.

The task of the RDBMS is to efficiently find and aggregate the required subset of data within the large database that resides on the server and return the small result set to the client. The task of the client reporting tool is to take this resulting dataset and format it in a way that the end user wants. For example, show dollar sign in front of money amounts, round the values to the necessary precision, print out values in the form of crosstabs with subtotals, etc. Pivoting is a common wide-spread need and there are plenty of reporting tools that can format small subsets of data in any way you like, but these tools are not database management systems.

Yes, RDBMSs in general and SQL Server in particular have some capabilities to work with dynamic database schema, but they are often clunky and ugly, such as dynamic SQL and PIVOT syntax. They were not designed for it.

Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
  • I can agree that such is the concept (or lack of concept) now unless Microsoft team finally comes up with an easy pivoting feature, which I am sure they finally will. Or are you saying that Microsoft is not allowed to improve that feature? You might say back in the 1990s that such is the concept of operating systems that we have to write commands in DOS. Luckily we do not have to. – Przemyslaw Remin Jun 03 '19 at 09:13
  • @PrzemyslawRemin, I don't think OS GUI is a good analogy.Somehow people continue to carry around a clunky heavy inflexible set of fixed-sized spanners, even though there is an adjustable wrench.You can write a program in a language that is compiled into executable machine code and in a language that is interpreted line by line.Different tools for different tasks.The concept of structured, normalised data when the type and number of columns in tables is known in advance is at the foundation of the SQL Server implementation.There may be other databases/tools that fit your task better - use them. – Vladimir Baranov Jun 03 '19 at 10:35
  • BTW, Microsoft added some support for R and other non-relational tools/languages in latest versions of SQL Server. I don't know the details, but you may find it useful. – Vladimir Baranov Jun 03 '19 at 10:37
  • @PrzemyslawRemin, "are you saying that Microsoft is not allowed to improve that feature?" MS can do what they want and sometimes they even listen to their current and future customers and add requested featured to their products. I doubt that it would be technically easy for MS to add support of dynamic pivot to SQL Server. I think that it would be really hard. And this feature is rather niche, in my opinion. So, I think that the chances that MS will implement it in foreseeable future are very low. – Vladimir Baranov Jun 03 '19 at 10:54
  • The phrase `SQL pivot` returns 7,010,000 results in 0.56 seconds. As for the measure of niche. – Przemyslaw Remin Jun 03 '19 at 11:00
0

You may use dynamic SQL queries and execute your desired result in single query. But first we'll prepare different component of our query in different variables after that combine all of them together to make a single query as per our desired output result.

You'll declare your query in a variable like @query of varchar type. By adding different component in your query you'll create any dynamic query which is not depend on single table but perform similar operations for any table name or parameters pass in it.

After building your query you can execute your query by using exec or exec sp_executesql and get the desired result.

DarkRob
  • 3,843
  • 1
  • 10
  • 27
  • What do you suggest? Building a dynamic query like in the answer I link to in my question? That query has at least 30 lines of code. My question is intended on how to search for a one-liner. Please check out how simple the syntax is in R. See different answers in R https://stackoverflow.com/questions/18622854/how-to-create-a-pivot-table-in-r-with-more-than-3-variables – Przemyslaw Remin May 31 '19 at 14:41