Wierd Group By Question !!!!! - SQL Server Forums

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)

Wierd Group By Question !!!!!

Author

Topic

tareq
Starting Member

3 Posts

Posted - 2010-06-12 : 17:50:07

Hi All,

i 've a returned data set in this format :(2 columns table)

Name Available_code
---- ----------------
Ali 1
Ali 2
Ali 3
Ali 4
Samy 1
Samy 2
Samy 3
Samy 4
Mona 5
Mona 6
Mona 7
Tom 5
Tom 6
Tom 7
-------------------------
so, i want some kind of grouping to group the names that are common in their codes, for example , i want a report that tells me :

ali,samy : 1,2,3,4
Mona,tom : 5,6,7

i dont know how the result set maybe looks like !!!
so i told you the logic i want.

note : if its applicable in Access , maybe good.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-06-12 : 23:54:39

[code]
declare @MyTable table (Myname varchar(9),Myid int)
insert into @MyTable
select 'Ali', 1 union all
select'Ali', 2 union all
select'Ali', 3 union all
select'Ali', 4 union all
select'Samy', 1 union all
select'Samy', 2 union all
select'Samy', 3 union all
select'Samy', 4 union all
select'Mona', 5 union all
select'Mona', 6 union all
select'Mona', 7 union all
select'Tom' ,5 union all
select'Tom', 6 union all
select'Tom', 7

SELECT DISTINCT
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.Myname FROM @MyTable AS s2 WHERE s2.Myid = s1.myID ORDER BY ',' + s2.Myname FOR XML PATH('')), 1, 1, '') + ' : ' + STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + cast(s2.Myid as varchar(2)) FROM @MyTable AS s2 WHERE s2.Myname = s1.Myname ORDER BY ',' + cast(s2.Myid as varchar(2)) FOR XML PATH('')), 1, 1, '') AS MyList
FROM @MyTable AS s1

[/code]

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources