Group By SSIS

Hi Experts,

I have some data inside a database and i would like to group them by Gender if possible.

For example there is around 300 entries mixture of women and men.

I would like to be able to Group them by the M and F and then sort them out accordingly into groups. where something 1 and something 2 are columns.

This data ideally should be exported to an excel file if possible. Many thanks for your time.



for example
                                    SOMETHING 1                 SOMETHING 2
M          
F


My question is can you use SQL server standard query of "group by" or is there something special you need to do?

Any examples would be fantastic or an explanation on how to do it.
LVL 6
NeoAshuraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sventhanCommented:
select gender, count(something1) s1,count(something2) s2
from your_table
group by gender

What is something1 and something2?

Post some data and expected result that would help us to construct a SQL for you.
0
Alpesh PatelAssistant ConsultantCommented:
Hi You can use Conditional split task and get the male and female record in different sets. After that sort it.
0
NeoAshuraAuthor Commented:
sven i think u hit the nail on the head, ill this out tomorrow and get back to you thanks again.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

NeoAshuraAuthor Commented:
Hi guys, The problem im having now, is im using an OLD source first, It then goes to a sort where i sort it by gender. It then goes to an aggregate so i can group them by nationality of english french spanish etc. They are grouped by GENDER and the nationality is COUNTED in the Aggregate to find out the numbers.

I then need this exporting to a CSV file format of the following
Gender         English        German         French
M                     100            105                 201
F                    254                 256            115

Can anyone help me solve this?
0
Reza RadConsultant, TrainerCommented:
could you put sample input data and sample output data here?
0
NeoAshuraAuthor Commented:
Input

Gender   Nationality
M             German
F              French
M              French
F              English
M             German
F              English

Output Desired for excel
              German          French       English
M                 5                   10              6
F                  3                     4            13

Does that help??
0
Reza RadConsultant, TrainerCommented:
OK, seems that you need to group by data first and then Pivot it.
for group by you can use this query:
select Gender,Nationality ,count(*) as Cnt
from myTable
group by Gendar,Nationality

this will result :

Gender               Nationality             Cnt
M                        German                  2
F                         French                    1
M                        French                     1
F                         English                    2



then you can Pivot it .

this is last query:

select
M,F,
[German],[French],[English]
from
(
select Gender,Nationality ,count(*) as Cnt
from myTable
group by Gendar,Nationality
) as sourceTable
PIVOT
( count(*)
for Nationality in ([German],[French],[English])
) as PivotTable

try it and let me know result.

0
NeoAshuraAuthor Commented:
Stupid question i guess but where does the query go on the pivot transform?
0
Reza RadConsultant, TrainerCommented:
your desired output can be generate without pivot transform.
just try the query as source without any transform(pivoting and grouping done within t-sql)
0
NeoAshuraAuthor Commented:
Apprently there is incorrect syntax

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '*'.
0
Reza RadConsultant, TrainerCommented:
try this one:

select
M,F,
[German],[French],[English]
from
(
select Gender,Nationality ,count(*) as Cnt
from myTable
group by Gendar,Nationality
) as sourceTable
PIVOT
( count(Cnt)
for Nationality in ([German],[French],[English])
) as PivotTable
0
NeoAshuraAuthor Commented:
Just curious, I have another project, I know the word "manual" is in the list of fields in that one. But i also know that manual is an SQL command how would i get around that??

Just trying your Query now.
0
Reza RadConsultant, TrainerCommented:
you can surround your field names with [] , like : select [manual] from ...
0
NeoAshuraAuthor Commented:
It works kind of but not how it needs to work it shows as

Gender                English         German         French

F                            1                   0                    0
F                             0                  1                     0
F                             0                  0                     1
M                            1                   0                     0
M                             0                   1                    0
M                              0                  0                   1

When it should show as
                                             English             German               French
F                                                   3                     5                     1
M                                                    5                    4                     2

Any Ideas?
0
sventhanCommented:
see if this helps....

select gender, count(case when Nationality = 'German' then 1 else 0 end) as German
,count(case when Nationality = 'French' then 1 else 0 end) as French
,count(case when Nationality = 'English' then 1 else 0 end) as English
from your_table
group by gender
0
Reza RadConsultant, TrainerCommented:
sorry, query has few problems,

this will led results you need:


select
Gender,
[German],[French],[English]
from
(
select Gender,Nationality ,count(*) as Cnt
from myTable
group by Gender,Nationality
) as sourceTable
PIVOT
( max(Cnt)
for Nationality in ([German],[French],[English])
) as PivotTable
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NeoAshuraAuthor Commented:
Hi Reza,

Kind of worked again

Getting closer

But it still did the following
                   English               French            German
F                   50              
F                                               100                  
F                                                                     15
M                   75                        90                    85
M                Etc
M


Is there anyway just to have
               Engish                 French        German

F      1000                       150                   75
M             50                        99                  15

Only once?

Thanks again

sven im looking at your code now.
0
Reza RadConsultant, TrainerCommented:
did you changed the code?
if yes, put your own version here,

I tried the code and it is working in my side.
0
NeoAshuraAuthor Commented:
here u go.

Diffrent columns same idea.
select
Gender
[Professional],[Manual],[Clerical],[Management],[Skilled_Manual]
from
(
select Gender,EnglishOccupation,SpanishOccupation,FrenchOccupation ,count(*) as Cnt
from targetmail_as_table
group by Gender,EnglishOccupation,SpanishOccupation,FrenchOccupation
) as sourceTable
PIVOT
( max (Cnt)
for EnglishOccupation in ([Professional],[Manual],[Clerical],[Management],[Skilled_Manual])

) as PivotTable

Open in new window

0
NeoAshuraAuthor Commented:
Omg ive cracked it at last!!

Sorted thanks.

Stupid mistake i did.. Will awrd points accordingly.
0
Reza RadConsultant, TrainerCommented:
your query is very different than mine !!!!!!!!!

put structure of targetmail_as_table here.
also sample data rows completely,

and I'll try to make query as you want.
0
Reza RadConsultant, TrainerCommented:
OK,

Glad to solve,
Regards,
0
sventhanCommented:
okie...

my version is like below..

;with cte as
(select 'M' as Gender, 'German' as Nationality union all
select 'F' as Gender, 'French' as Nationality union all
select 'M' as Gender, 'French' as Nationality union all
select 'F' as Gender, 'English' as Nationality union all
select 'M' as Gender, 'German' as Nationality union all
select 'F' as Gender, 'English' as Nationality )

select x.gender, max(case when Nationality = 'German' then x.cnt else 0 end) as German
,max(case when Nationality = 'French' then x.cnt else 0 end) as French
,max(case when Nationality = 'English' then x.cnt else 0 end) as English
from
(select gender,nationality,count(*) cnt
from cte
group by gender,nationality) x
group by gender
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.