Group by Query for following requirement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Group by Query for following requirement

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-03-09 : 07:35:47

Hi, i have following table

Name Culture Gender
ABC INDI M
ABC CHIN M
DEF JPAN F
FGH INDI F
DEF GERM M

From the above table, i need output as below. i.e the count of names is > 1 then i need to know under which culture they are exitsin with what gender

Name count Culture CultureGender
ABC 2 INDI-JPAN INDI-M,CHIN-M
DEF 2 JPAN_GERM JPAN-F,GERM-M

I tried this with following query.
Select Name, Count(Name) from table group by Name having Count(Name) > 1

But i am not knowing how to get the culture under groupby. the important thing is table has no prirmary or unique key.

G. Satish

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-03-09 : 07:55:37

Assume that your table is Countries, try the below query

with cte as
(select [Name],Count([Name]) as MyCount from COuntries Group by [Name] )
select *,stuff( (select ',' + [Culture] from Countries where [Name] = cte.[Name] for xml path('')) ,1,1,'') as Countries
,stuff( (select ',' + [Culture] + '-' + Gender from Countries where [Name] = cte.[Name] for xml path('')) ,1,1,'') as Countries
from cte where MyCount > 1

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-03-09 : 08:07:19

It works fine. Thank you

quote:
Originally posted by ddramireddy

Assume that your table is Countries, try the below query

with cte as
(select [Name],Count([Name]) as MyCount from COuntries Group by [Name] )
select *,stuff( (select ',' + [Culture] from Countries where [Name] = cte.[Name] for xml path('')) ,1,1,'') as Countries
,stuff( (select ',' + [Culture] + '-' + Gender from Countries where [Name] = cte.[Name] for xml path('')) ,1,1,'') as Countries
from cte where MyCount > 1

G. Satish

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources