December 28, 2013 at 10:29 am
Hi,
Using SQL Server 2000 :
create table test
(
group_name varchar(30),
name varchar(30),
add1 varchar(30)
)
insert into test values ('grp1','abc','city 1')
insert into test values ('grp2','aaa','city 2')
insert into test values ('grp3','bbb','city 3')
insert into test values ('grp4','ccc','city 4')
insert into test values ('grp1','ddd','city 5')
insert into test values ('grp2','eee','city 6')
insert into test values ('grp3','aaa','city 2')
insert into test values ('grp1','ddd','city 5')
insert into test values ('grp3','bbb','city 3')
insert into test values ('grp3','aaa','city 2')
insert into test values ('grp4','bbb','city 3')
insert into test values ('grp4','aaa','city 2')
insert into test values ('grp2','abc','city 1')
insert into test values ('grp2','ccc','city 4')
insert into test values ('grp2','ddd','city 5')
Select * from test
Group_NameNameCity
grp1abccity 1
grp2aaacity 2
grp3bbbcity 3
grp4ccccity 4
grp1dddcity 5
grp2eeecity 6
grp3aaacity 2
grp1dddcity 5
grp3bbbcity 3
grp3aaacity 2
grp4bbbcity 3
grp4aaacity 2
grp2abccity 1
grp2ccccity 4
grp2dddcity 5
Required Output : If I am going to get group_name=grp1 then :
NameCityGrp2Grp3Grp4
abccity 11
dddcity 51
<Total>200
Because I am going to fetch where group_name='grp1', so only two person are there. Person abc, is also member of Grp2, so in the grp2 column there should be 1, same person ddd is also member of group_name grp2, so in the grp2 column there should be 1 and in the last, totals of groups.
Suppose I am going to get group_name=grp2 then :
NameCityGrp1Grp3Grp4
aaacity 221
eeecity 6
abccity 11
ccccity 41
dddcity 52
<Total>322
There are aaa,eee,abc,ccc and ddd person are in grp2, so first we fetch their name. Now aaa, is also member of grp3, have 2 times and 1 time in grp4. Person eee, is only member of grp2, so nothing in grp1, grp3 and grp4. Person abc is also member of grp1 so in the grp1 column there should 1 (how many time). Person ccc is 1 time in grp4. Same person ddd is also member of grp1(have 2 rows in it) so, 2 in the grp1 column... like that and in the last totals of groups.
I have no idea, how do I will be able to get in SQL Server 2000 Enterprise Edition.
Thanks and Regards
Girish Sharma
December 28, 2013 at 5:00 pm
nevermind...
Are you looking for a crosstab? I'm just not very clear on your requirements.
December 28, 2013 at 10:07 pm
Suppose I wish to get the name of persons who are in group_name=2 and I got :
aaa,eee,abc,ccc and ddd.
Now, aaa person also belongs to grp3 and grp4, so in the query output grp3 column should have 2 (because 2 times) and in the grp4 column should have 1 (1 times in the table).
For eee person, who belongs to only grp2 for which current query is running, but he do not belongs to any other group, so null or zero in grp1, grp3 and grp4 columns.
Person abc also belongs to grp1, so in the query output column grp1 should have 1 (1 time of grp1).
Person ccc also belongs to grp4, so in the query output column grp4 should have 1 ( 1 time of grp4).
Person ddd also belongs to grp1, so in the query output column grp1 should have 2 (2 time of grp1).
I hope now I am clear and please help me.
December 28, 2013 at 10:49 pm
I tried with below SQL it worked :
select a.name,max(b.grp1) grp1,max(b.grp2) grp2,max(b.grp3) grp3,max(b.grp4) grp4 from test a
inner join
(
select
min(name) as name,
sum(case group_name when 'grp1' then 1 else 0 end) as grp1,
sum(case group_name when 'grp2' then 1 else 0 end) as grp2,
sum(case group_name when 'grp3' then 1 else 0 end) as grp3,
sum(case group_name when 'grp4' then 1 else 0 end) as grp4
from test
group by name
) b
on a.name=b.name
where a.group_name='grp1'
group by a.name
abc1100
ddd2100
select a.name,max(b.grp1) grp1,max(b.grp2) grp2,max(b.grp3) grp3,max(b.grp4) grp4 from test a
inner join
(
select
min(name) as name,
sum(case group_name when 'grp1' then 1 else 0 end) as grp1,
sum(case group_name when 'grp2' then 1 else 0 end) as grp2,
sum(case group_name when 'grp3' then 1 else 0 end) as grp3,
sum(case group_name when 'grp4' then 1 else 0 end) as grp4
from test
group by name
) b
on a.name=b.name
where a.group_name='grp2'
group by a.name
aaa0121
abc1100
ccc0101
ddd2100
eee0100
But, I don't want the column when grp1 when I am saying for group_name='grp1', and don't want grp2 column when I am saying where group_name='grp2' like that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply