May 19, 2011 at 7:01 am
Hi All,
How do i generate the below output in sql server
age sex
10 M
11 M
12 F
13 M
15 M
16 F
17 M
18 M
19 F
20 F
Output
age range M F
10-12 2 1
13-15 2 0
16-18 2 1
19-20 0 2
thanks all
May 19, 2011 at 7:08 am
Hi
Have a look at PIVOT:
http://www.techrepublic.com/article/write-pivot-queries-in-sql-server-2005/6143761
May 19, 2011 at 7:19 am
How do i do it without pivot
May 19, 2011 at 7:24 am
I have just asked a similar question in the SQL 2005 forum and a chap called Wayne very kindly showed me how to do it there.
May 19, 2011 at 7:29 am
If you have a table that identifies the ranges that each age are in, then it's pretty easy
declare @t table (age tinyint, sex char(1))
declare @grouping table (age tinyint, groupid tinyint)
insert into @t values
(10,'M'),
(11,'M'),
(12,'F'),
(13,'M'),
(15,'M'),
(16,'F'),
(17,'M'),
(18,'M'),
(19,'F'),
(20,'F')
insert into @grouping values
(10,1),
(11,1),
(12,1),
(13,2),
(15,2),
(16,3),
(17,3),
(18,3),
(19,4),
(20,4)
select cast(min(t.age) as char(2)) + '-' + cast(max(t.age) as char(2)) AgeRange,
sum(case when t.sex = 'M' then 1 else 0 end) Male,
sum(case when t.sex = 'F' then 1 else 0 end) Female
from @t t inner join @grouping g
on t.age = g.age
group by groupid
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 2:33 pm
imfairozkhan (5/19/2011)
Hi All,How do i generate the below output in sql server
age sex
10 M
11 M
12 F
13 M
15 M
16 F
17 M
18 M
19 F
20 F
Output
age range M F
10-12 2 1
13-15 2 0
16-18 2 1
19-20 0 2
thanks all
Are your age ranges always going to be ranges of 3 years ? What age group do you need to go to ? You mention you are not able to use pivot. Why are you not able to use pivot ?
If you are still not able to use pivot you will probably need to do this in a multiple step process in getting your counts per age range and sex placing these into a a work table and then use a cursor to step through to get your final result set.
Normally cursors are bad but this is a very small result set to work with if you are not able to use Pivot as previously stated by others in this post.
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
June 3, 2011 at 4:17 am
age sex
10 M
11 M
12 F
13 M
15 M
16 F
17 M
18 M
19 F
20 F
Output
age range M F
10-12 2 1
13-15 2 0
16-18 2 1
19-20 0 2
create table age(age tiny int,sex varchar(3))
insert into age values(10,'M'),
(11,'M')
(12,'F')
(13,'M')
(15,'M')
(16,'F')
(17,'M')
(18,'M')
(19,'F')
(20,'F'),
Answer:
select age1 as agerange,case when M IS null then 0 else M end as Male,case when F is null then 0 else f end as Female from
(
select age1,sex,COUNT(*) as [Count] from
(select age1=case when age between 10 and 12 then '10-12'
when age between 13 and 15 then '13-15'
when age between 16 and 18 then '16-18'
when age between 19 and 20 then '19-20' end,
se1=case when sex='M' then '1' else '2'end,sex from age
GROUP by age,sex)p
group by age1,se1,sex
)up
PIVOT
( SUM([count]) FOR sex IN (M, F)) AS pvt
REsult:
agerange Male Female
10-12 2 1
13-15 2 0
16-18 2 1
19-20 0 2
--
Thanks & Regards,
Ravindra Babu S
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply