CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3
hitesh israni
hitesh israni

Reputation: 1752

Dynamic columns in sql query

In some application, I am using following sql query,

SELECT DATE, 
SUM( CASE WHEN project_id =  '6' THEN spent_time  ELSE 0 END ) project_1, 
SUM( CASE WHEN project_id =  '41' THEN spent_time  ELSE 0 END ) project_2, 
SUM( CASE WHEN project_id =  '14' THEN spent_time ELSE 0 END ) project_3
FROM tasks
WHERE user_id =80
GROUP BY DATE, project_id

it returns following output

enter image description here

but in the above SQL query I have used predefined columns. I am looking for a solution where I have dynamic columns say I don't have project ids known before hand.

Also I want one extra columns that shows sum of project_1, project_2, project_3 for each row.

So, the resultant structure will be DATE, PROJECT_1, PROJECT_2, PROJECT_3, SUM_OF_PROJECTS

Can I also get data where the row doesn't exists? For instance 2012-04-03 in the below example.Preferably not using calendar table

Any pointers would be appreciated.

other details: Database- mysql, Rails(2.3.14)

Upvotes: 3

Views: 1210

Answers (3)

Richard Fawcett
Richard Fawcett

Reputation: 2809

I'm not sure what database engine you're using, but the sample below uses Microsoft SQL Server. I'm sure it could be adapted quite easily to work with other engines.

To start with, I created some setup data using the following queries:

create table tasks(
[id] int not null identity(1,1),
[DATE] smalldatetime not null,
project_id int not null,
spent_time int not null,
primary key ([id])
)
go
insert into tasks([date],project_id,spent_time)
select '2012-04-02',1,10
union all select '2012-04-02',1,5
union all select '2012-04-02',2,5
union all select '2012-04-03',1,8
union all select '2012-04-03',2,1
go

As has been mentioned in comments above, you need to generate a SQL statement dynamically. I do this into the variable @sql, before executing it at the end. Here's my solution:

declare @sql nvarchar(4000), @project_id nvarchar(10)
select @sql = 'select [date]'

declare c cursor for select distinct convert(nvarchar(10),project_id) as project_id from tasks order by project_id
open c
fetch c into @project_id
while @@FETCH_STATUS = 0
begin
    select @sql = @sql + ', sum(case when project_id = ' + @project_id + ' then spent_time else 0 end) as project_' + @project_id
    fetch c into @project_id
end
close c
deallocate c

select @sql = @sql + ', sum(spent_time) as sum_of_projects from tasks group by [date] order by [date]'
exec (@sql)

As expected from my test data, it generates the output:

date        project_1  project_2
----        ---------  ---------
2012-04-02  15         5
2012-04-03  8          1

Hope this helps!

UPDATE

OP has stated a desire to avoid cursors, so the following code also works (on MS SQL Server at least) without using a cursor...

declare @sql nvarchar(4000), @project_id nvarchar(10)
select @sql = 'select [date]'

select @sql = @sql + ', sum(case when project_id = ' + project_id + ' then spent_time else 0 end) as project_' + project_id
from (select distinct CONVERT(nvarchar(10), project_id) as project_id from tasks) q

select @sql = @sql + ', sum(spent_time) as sum_of_projects from tasks group by [date] order by [date]'
exec (@sql)

Upvotes: 2

MatBailie
MatBailie

Reputation: 86706

You need to write code the writes SQL. There is no way to have a single static SQL query that returns a dynamic number of columns. Only by dynamically building the query itself can you accomplish that.

Your example code is fine. For a fixed number of projects.

You could even make '6', '41' and '14' into parameters, meaning that your query will always return three projects, but they could be different projects each time.

But to then have a fourth project, you'd need to change the query.

You could write code to make that change on the fly (dynamic SQL), or just work out the most columns you ever need at once, and then live with that limitation.

Upvotes: 1

amaters
amaters

Reputation: 2316

You could try using pivot. Check http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx for an example

Upvotes: 0

Related Questions