SQL PROBLEM
-
Hello everyone. I have a problem with an sql
I need to become an overview of all projets from all partners
The table must look like this
Partner | Project1| project2| project3| prject4......
1 | x | x | | x
2 | | x | x |
3........
I have on table with 2 columns
column 1 is IDPartner
column 2 is IDProjekt
I have no idea how to do it.
I have n partners and n Projets
Does anybody know how I can do that.
Thank you
-
Mirza - This looks like a dynamic pivot table requirement to me. The links and the code below should get you going in the right direction...
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx
http://www.sqlteam.com/item.asp?ItemID=2955
--data
create table #t (IDPartner int, IDProjekt int)
insert #t
select 1, 1
union all select 1, 2
union all select 1, 4
union all select 2, 2
union all select 2, 3
union all select 3, 1
union all select 3, 4
--calculation (static)
select
IDPartner as Partner,
case sum(case when IDProjekt = 1 then 1 else 0 end) when 0 then '' else 'x' end as Project1,
case sum(case when IDProjekt = 2 then 1 else 0 end) when 0 then '' else 'x' end as Project2,
case sum(case when IDProjekt = 3 then 1 else 0 end) when 0 then '' else 'x' end as Project3,
case sum(case when IDProjekt = 4 then 1 else 0 end) when 0 then '' else 'x' end as Project4
from #t
group by IDPartner
--calculation (dynamic - produces above code and executes it)
declare @s-2 varchar(8000) --note this limitation
declare @maxProjectId int
select @maxProjectId = max(IDProjekt) from #t
declare @i int
set @i = 1
while @i <= @maxProjectId
begin
set @s-2 = isnull(@s + ',' + char(13) + char(10), '') +
'case sum(case when IDProjekt = ' + cast(@i as varchar(10)) + ' then 1 else 0 end) when 0 then '''' else ''x'' end as Project' + cast(@i as varchar(10))
set @i = @i + 1
end
set @s-2 = 'select IDPartner as Partner, ' + @s-2 + ' from #t group by IDPartner'
--select @s-2
exec(@s)
drop table #t
/*results
Partner Project1 Project2 Project3 Project4
----------- -------- -------- -------- --------
1 x x x
2 x x
3 x x
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply