Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query help Pivot and Sum Expand / Collapse
Author
Message
Posted Saturday, December 28, 2013 10:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 1, 2014 2:28 AM
Points: 47, Visits: 110
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_Name	Name	City
grp1 abc city 1
grp2 aaa city 2
grp3 bbb city 3
grp4 ccc city 4
grp1 ddd city 5
grp2 eee city 6
grp3 aaa city 2
grp1 ddd city 5
grp3 bbb city 3
grp3 aaa city 2
grp4 bbb city 3
grp4 aaa city 2
grp2 abc city 1
grp2 ccc city 4
grp2 ddd city 5


Required Output : If I am going to get group_name=grp1 then :
Name	City	Grp2	Grp3	Grp4
abc city 1 1
ddd city 5 1
<Total> 2 0 0


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 :
Name	City	Grp1	Grp3	Grp4
aaa city 2 2 1
eee city 6
abc city 1 1
ccc city 4 1
ddd city 5 2
<Total> 3 2 2


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
Post #1526306
Posted Saturday, December 28, 2013 5:00 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 9:46 PM
Points: 746, Visits: 4,793
nevermind...

Are you looking for a crosstab? I'm just not very clear on your requirements.
Post #1526319
Posted Saturday, December 28, 2013 10:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 1, 2014 2:28 AM
Points: 47, Visits: 110
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.
Post #1526323
Posted Saturday, December 28, 2013 10:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 1, 2014 2:28 AM
Points: 47, Visits: 110
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

abc	1	1	0	0
ddd 2 1 0 0


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

aaa	0	1	2	1
abc 1 1 0 0
ccc 0 1 0 1
ddd 2 1 0 0
eee 0 1 0 0


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.
Post #1526324
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse