SQL Server: array of columns

Question

Ok I have been pondering the idea of dynamic columns, what I mean by this is a table of columns named January......December.

User at runtime would select a month range such as Feb to July.

What I would normally do is fetch all columns from my code behind then sort the ones I need and ones I don't.

But I was wondering a way to do this all in SQL.

I know SQL doesn't support arrays (sad face) but there are alternatives.

So my question is, is there a way for a SQL query to be set-up that dynamically selects a number of columns depending on a parameter that is a range of column names?

I've looked at some dynamic query's, but they only do 1 column.

I'm thinking along the lines of passing a string of columns provided by the code behind as 1 param in the SQL, then somehow iterate through to select each column.

What do you guys think? cant be Done? can be done but messy?

EDIT: thought I would provide some code, as you can see I apply a pivot query and get a range of columns (months). What's being suggested so far is to normalise first then pivot.

SELECT
   [1] January ,
   [2] February,
   [3] March,
   [4] April,
   [5] May,
   [6] June,
   [7] July,
   [8] August,
   [9] September,
   [10] October,
   [11] November,
   [12] December
   FROM
(
  SELECT MONTH(Convert(datetime,[lasttaken],120)) as months, complete
  FROM #Temp WITH(NOLOCK)
) d
pivot 
(
   count(complete)
   for months in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) p

not sure what you mean, im not asking for opinions, im asking for solutions. i.e one answer is to normalise before i get to this stage then use a pivot query after. I fail to see how this is a bad question? — lemunk
– lemunk, Commented Aug 15, 2013 at 11:14

Binary Worrier · Accepted Answer · 2013-08-16 08:36:08Z

5

There is a way to do this, but involves restructuring your table.
Your problem is that the (month) column names are meta data, and you want to see them

Instead of

year | Jan | feb | Mar | Apr | etc  
2013 | 10  | 9   | 11  | 4   | 0

as actual data.

What you want to do is make a column for month, and normalize your data to look like this.

year | month | data
2013 | 1     | 10
2013 | 2     | 9
2013 | 3     | 11
2013 | 4     | 4

Now you can query the data by month.

This is how you have to approach data design to be able to query the data the way you want to.

Hope this helps.

edited Aug 16, 2013 at 8:36

answered Aug 15, 2013 at 11:13

Binary Worrier

51.8k20 gold badges144 silver badges188 bronze badges

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

3 Comments

lemunk Over a year ago

yeh coming to realize this now, ill provide some code, the code provided is a pivot query that gets all months from the lasttaken date. Whats being suggested is that before this i need to normalise and then pivot to achieve the result?

Binary Worrier Over a year ago

@Steven: Yes, thats pretty much it. How you need to store it and how you need to present it are two very different things.

lemunk Over a year ago

yes seems I was focused on presentation rather than how it should be normalized, great answer as are the rest!

Gordon Linoff · Accepted Answer · 2013-08-15 11:05:08Z

3

And what is the problem with storing data in a normalized form -- a separate row for each month? Then, when you want to fetch a particular range, just use the where clause.

SQL Server offers the pivot functionality, which makes it pretty easy to put row values into columns.

answered Aug 15, 2013 at 11:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

S.M · Accepted Answer · 2013-08-15 11:21:57Z

0

you can create dynamic sql statement with required columns by passing it to EXECUTE(@query) statement. for example

Execute ('select Jan, Feb, Mar from Table')

as suggested by others you need to normalize your table and then do where clause to select records for specific period.

edited Aug 15, 2013 at 11:21

answered Aug 15, 2013 at 11:16

S.M

7763 silver badges8 bronze badges

Collectives™ on Stack Overflow

SQL Server: array of columns

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related