I'm sure it's possible, but the best way for me to help is if you can provide a dbfiddle link. Create a table, insert some representative data, and even include the current version of your query. Otherwise, I may have to make some poor guesses and the answer wouldn't work when you try to implement it.
Ok sure and to be sure, if once I create these DDLs, I grab the URL while the session is still open and paste here for you?
That's where things got screwy last time.
I will then post the current working code.
Thanks again for all your help
Hi @SqlHippo , please see the link below:
I am leaving the dbfiddle page open in case what I sent you is not what you are looking for.
You can easily fill in 'Submitted' for those with a dateCreated value, but it's more challenging to also fill in the NULLs with 'No Data'.
This is where I'll stop and say: building these dynamic pivot tables is a presentation-layer task. Once you go beyond the basic abilities of PIVOT, you just start banging your head on the concrete. Excel, PowerBI, Tableau, or any other front end can typically do this stuff way easier.
That said, it can be done. It just means you need to take the @cols string from the outer query and change it from [2018], [2019], ... to ISNULL([2018], 'No Data') AS [2018], ISNULL([2019], 'No Data') AS [2019], ...
This is where you really start throwing time down the toilet with every little presentational change someone requests.
1 Like
Hahahaha,
Ok much appreciation for your time and for pointing me in the right direction with the Year(datecreated) bit.
I was able to finally resolve it from my asp.net app.