Geek asks and answers

Dynamic columns in sql query

In some applications, I use the 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 the following output

enter image description here

but in the above SQL query, I used predefined columns. I am looking for a solution where I have dynamic columns that say that I do not have the project identifiers that were known before.

I also need another column that shows the sum of project_1, project_2, project_3 for each row.

Thus, the resulting structure will be DATE, PROJECT_1, PROJECT_2, PROJECT_3, SUM_OF_PROJECTS

Can I also get data where the row does not exist? For example, 2012-04-03in the example below. Prefer not to use a calendar table

Any pointers would be appreciated.

other information: Database-mysql, Rails (2.3.14)

+3
3

write SQL. SQL-, . .

. .

"6", "41" "14" , , , .

, , .

, " " ( SQL) , , .

+1

, , Microsoft SQL Server. , .

, :

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

, SQL . @sql, . :

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)

, :

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

, !

UPDATE

OP , ( , MS SQL Server) ...

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)
+2

More articles:


All Articles