Use Column Values as Columns dynamically

Question

I have a query that uses a bunch of case statements to aggregate sales by Division and Employee Id with the month and year as the column headings. Currently, I am using Case statements for each unique month and year. My goal is to try to find a more dynamic method to achieve the same result without having to add additional case statements for every new value that ends up in the Mnth_Yr column.

I have tried to use PIVOT, but the results were that all outcome was that all the row values were converted to columns instead of just the Mnth_Yr column.

Here is a sample of the data.

This is the desired results I am looking to achieve.

Here is the code that I am using.

    IF OBJECT_ID('tempdb.dbo.#table_data') IS NOT NULL
    DROP TABLE #table_data
    
    CREATE table #table_data
    ([Division] varchar(15), [Emp_Id] int, [Mnth_Yr] int, [Sales] money)
    
INSERT INTO #table_data
    ([Division], [Emp_Id], [Mnth_Yr], [Sales])
VALUES
    ('Northeast', 625, 202101, 310.58),
    ('Northeast', 627, 202101, 252.83),
    ('Southeast', 158, 202101, 5871.12),
    ('Southeast', 487, 202101, 1587.58),
    ('Northeast', 625, 202102, 521.87),
    ('Northeast', 627, 202102, 870.24),
    ('Southeast', 158, 202102, 7812.17),
    ('Northeast', 625, 202103, 564.57),
    ('Northeast', 627, 202103, 251.61),
    ('Southeast', 158, 202103, 15780.45),
    ('Southeast', 487, 202103, 1770.51)
;
----------------------------------------------------------------------

select Division
, Emp_Id
, SUM(Case When Mnth_Yr = 202101 Then Sales ELSE null END) as "202101"
, SUM(Case When Mnth_Yr = 202102 Then Sales ELSE null END) as "202102"
, SUM(Case When Mnth_Yr = 202103 Then Sales ELSE null END) as "202103"

from #table_data
Group by Division
, Emp_Id

Tag your question with the database you are using. What is the issue with your query? Your code seems to work: dbfiddle.uk/…. — Gordon Linoff
– Gordon Linoff, Commented Apr 8, 2021 at 16:40

Caius Jard · Accepted Answer · 2021-04-08 16:58:42Z

My goal is to try to find a more dynamic method to achieve the same result without having to add additional case statements for every new value that ends up in the Mnth_Yr column.* -

You kinda can't, unless you use dynamic SQL.. There's a reason for this; a database table's columns are like attributes of things - they aren't intended to be dynamic and they're hard to work with if they are. Other systems that use your database expect there to be fields like Total, Name, Address on your Order table.. They don't really know what to do with an Order that this week doesn't have a NumberofItemsFulfilledInFirstWeekOfJan and next week does. Writing systems all the way up the chain that support dynamic properties of things, right the way up to the human user, are quite hard work

I sugest you look to range your data, instead of providing columns of 202101, 202102, consider providing fixed names that have some meaning year on year; call them Jan, Feb, Mar.. Use a where clause that selects data based on the current year (by parameter or server clock), and let that give context to what "Jan" means

If you don't want to do this, that's fine - but keeping to the "number of columns output by a query is not intended to vary dynamically depending on what day it is, or what happened recently" mantra helps develop database driven software - it's the number of rows that should vary, not the number of columns. You're looking to use the database as a presentation tool, when really this pivoting operation should be carried out by the charting software in your front end app - it can receive the variable number of rows and fixed number of columns and churn out the grid

If you're dead set on making the DB do this and making your columns infinitely variable, then you'll need to write code to generate the query dynamically, and run it, and then consume it - that can be in-db in something like a stored procedure, or in a front end language that queries for the number of variations, and concats together a SQL string and runs it, and outputs the result..

..but there isn't a way to say to your db "use this fixed sql query to pivot this data on x and give me a resultset with y number of columns today, but z tomorrow"

ollyjolly · Accepted Answer · 2021-04-08 18:09:27Z

0

Well it isn't quite the approach mentioned above, but it does what I need using XML.

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SET @cols = STUFF(
                 (
                     SELECT DISTINCT
                            ','+QUOTENAME(c.Mnth_Yr)
                     FROM #table_data c FOR XML PATH(''), TYPE
                 ).value('.', 'nvarchar(max)'), 1, 1, '');
SET @query = 'SELECT Division, Emp_Id, '+@cols+'from (SELECT Division,
           Emp_Id,
           sum(sales) AS [amount],
           Mnth_Yr AS [category]
    FROM #table_data
    Group by Division,
           Emp_Id,
           Mnth_Yr
    )x pivot (max(amount) for category in ('+@cols+')) p';
EXECUTE (@query);

answered Apr 8, 2021 at 18:09

ollyjolly

236 bronze badges

Collectives™ on Stack Overflow

Use Column Values as Columns dynamically

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related