More than cross-tab
-
Need help to do this trick. There is a table with 3 columns: contributor, source, cnt and values- contr1 source11 cnt11; contr1 source12 cnt12; contr1 source13 cnt13; contr2 source21 cnt21; contr2 source22 cnt22; contr3 source31 cnt 31 (i.e. number of rows for each contributor can be different, number of contributors can be different also). For report purpose I would like to get result in following form: source11 cnt11 source21 cnt21 source31 cnt31; source12 cnt12 source22 cnt22; source13 cnt13. How to do this? Thanks
-
select source1= case when contributor = 'contr1' then source else NULL end,
cnt1= case when contributor = 'contr1' then cnt else NULL end,
source2= case when contributor = 'contr2' then source else NULL end,
cnt2= case when contributor = 'contr2' then cnt else NULL end,
source3= case when contributor = 'contr3' then source else NULL end,
cnt3= case when contributor = 'contr3' then cnt else NULL end
from yourtable
if the number of contributor varies, you have to use Dynamic SQL to do this. But it will be the same trick.
Why are you not doing this in your front end Reporting Tool ? It is definately easier to do it there.
-
select
c.source
,c.cnt
from
TempTable c
JOIN (select
a.Contributor
,a.source
,count(*) As CCount
from
TempTable a
left join TempTable b ON a.Contributor = b.Contributor
and a.source >= b.source
where
b.Contributor is not null
group by
a.Contributor
,a.source) d ON d.Contributor = c.Contributor
and d.source = c.source
order by
d.Ccount
,c.Contributor
Hope this solves ur issue
Diono
-
It looks like the formatting of your question made the request difficult to understand.
This does the trick (I think), but I've used an id column as a helper, so maybe that's cheating
Just run it and let us know if it helps...
declare @t table (id int identity(1, 1), contributor varchar(30), source varchar(30), cnt varchar(30))
insert @t
select 'contr1', 'source11', 'cnt11'
union all select 'contr1', 'source12', 'cnt12'
union all select 'contr1', 'source13', 'cnt13'
union all select 'contr2', 'source21', 'cnt21'
union all select 'contr2', 'source22', 'cnt22'
union all select 'contr3', 'source31', 'cnt31'
select a.contributor, a.source, a.cnt, b.source, b.cnt, c.source, c.cnt
from @t a
left outer join @t b on a.contributor = b.contributor and a.id < b.id
left outer join @t c on a.contributor = c.contributor and b.id < c.id
inner join (select contributor, sum(id) TotalId from @t group by contributor) d
on a.contributor = d.contributor and d.TotalId = a.id + isnull(b.id, 0) + isnull(c.id, 0)
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Am I missing something obvious here
SELECT source, cnt
FROM @table
GROUP BY source, cnt
ORDER BY SUBSTRING(source,8,1) ,source, cnt
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks a lot guys for your help. I used dynamic sql to do the trick (not too elegant but it works).
Ryan: contributors number can be different. David: source12 (etc)- value, not title. Diono: sorry man, but query returns not what I expect for.
Good luck.
-
As I said, I think the formatting of your question made the request difficult to understand.
I'd like to understand why what I gave wasn't appropriate though. 
Was it appropriate for your test data? And you say 'contributors number can be different', but what is a 'contributors number'?
Thanks...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Yuri?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply