Creating Dynamic number of columns in SQL Server

Question

I have the following data plus some other additional tables that I am working on.

id ClinicId   MonthsInOperation Budget Revenue  
1  1232       12                  2000
2  1534       8                   3500
3  1767       10                  2500
4  1232       13                  3000
5  1534       2                   4000

What I ultimately need to generate is(Table Structure):

ClinicInfo(Static Columns) + Selected Month------------->> End of Year(Dynamic Columns)

Dynamic Columns will have the budget information for specific month (For example Table structure + Data):

ClinicInfo(Static Columns) + Feb 2013-------------------->> December 2013
--------------------------   2000+-------+-------------------+ 4500

I haven't used PIVOT before, not sure if that is what I need here.

What's the relationship between MonthsInOperation, and a normal calendar? I see 13 in there, so that obviously represents year 2, month 1, but what is that in a real date? Is it querable, or will we have to rely on hardcode? — Jaaz Cole
– Jaaz Cole, Commented Jul 15, 2014 at 18:13
MonthsInOperation is basically the number of months the clinic has operated since the date it was open. so If clinic opened on Jan 15,2014: the MIO will be 6, if we compare it to today. — user3681350
– user3681350, Commented Jul 15, 2014 at 18:36
As implied by the column name, but I'm getting the implication from your post that you want that translated to a real (i.e. Dec 2013) date, so the question is, how? — Jaaz Cole
– Jaaz Cole, Commented Jul 15, 2014 at 19:36
@JaazCole I know the opening date of the clinic, so I can figure out the date in the form of Dec 2013. — user3681350
– user3681350, Commented Jul 15, 2014 at 20:36

Earl G Elliott III · Accepted Answer · 2014-07-15 20:15:48Z

2

Producing a report like this, directly from SQL Server is difficult at best. The problem with PIVOT is that it only works with a static column set. If you want your output columns to be dynamic, PIVOT (in SQL) will not help you there.

I am also assuming that you have some other table that has the budget info, broken down per month, for each clinic.

Honestly, doing this in a reporting solution of some type (Active Reports, SSRS, Crystal) using a header/detail type layout is probably the easiest way to skin it. Or building it in some non-SQL middle tier (Java, .NET, etc.) because then you could piece it together from the different pieces of data (basic clinic info, months in range, data for those months). You can do it in raw TSQL, but repeatable performance is sketchy at best in my experience.

Probably not the answer you were looking for, but hopefully gave you some ideas.

answered Jul 15, 2014 at 20:15

Earl G Elliott III

4252 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Matt Over a year ago

One could even connect Excel to the SQL Server data source, then run a Pivot Table off the dataset.

Matt Over a year ago

Otherwise, if SQL is the only way, build the PIVOT with all 12 months, regardless of whether they are needed or not.

Earl G Elliott III Over a year ago

You could also build your SQL dynamically, but performance will suffer.

user3681350 Over a year ago

@EarlGElliottIII I am using LogiXML as the reporting too,but It doesn't have any such capability. Any idea how should i proceed if want to go ahead with Dynamic SQL?

Earl G Elliott III Over a year ago

Ah, ok. You will need to do it in SQL direct then. You can do dynamic sql in sql, its just a little funky. Here is an example from elsewhere in stackoverflow stackoverflow.com/questions/12430512/…

Collectives™ on Stack Overflow

Creating Dynamic number of columns in SQL Server

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related