SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Group By Question?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 05/13/2013 :  09:26:31  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
Hi,

I have 2 tables as follows:

CREATE TABLE [dbo].[A](
	[MatchID] [nvarchar](100) NULL,
	[DrawName] [nvarchar](100) NULL,
	[MatchCode] [nvarchar](100) NULL,
	[SpecialEventCode] [nvarchar](100) NULL,
	[TeamNames] [nvarchar](100) NULL,
	[StartDate] [datetime] NULL,
	[Round] [nvarchar](100) NULL,
	[Status] [nvarchar](50) NULL,
	[StatusID] [nvarchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[B](
	[MatchID] [nvarchar](100) NULL,
	[MatchCode] [nvarchar](100) NULL,
	[SpecialEventCode] [nvarchar](100) NULL,
	[IncidentID] [nvarchar](100) NULL,
	[Team] [nvarchar](10) NULL,
	[Minute] [nvarchar](10) NULL,
	[Score] [nvarchar](50) NULL,
	[deleteInfo] [nvarchar](10) NULL,
	[Player] [nvarchar](100) NULL
) ON [PRIMARY]


When I use a query as follows:

SELECT Team, Minute, Player FROM [dbo].[B] b
WHERE MatchID =(SELECT MatchID FROM [dbo].[LS_A] where SpecialEventCode =1 and StartDate <= GETDATE() and StatusID in (2,3,4,8,9))order by Minute asc 



Here is a sample output:

Team	Minute	Player
1	44	Cenk
1	55	Cenk
2	58	Harun


Is there a way to group by the players with same name according to teams? I mean I would like to get the output as follows:


Team	Minute	Player
1	44,58	Cenk
2	58	Harun

stepson
Constraint Violating Yak Guru

Romania
434 Posts

Posted - 05/13/2013 :  09:59:36  Show Profile  Reply with Quote



;with T 
AS
( select 1 as Team, '44' as Minute, 'Cenk' as Player
 union all
  select 1 as Team, '55' as Minute, 'Cenk' as Player
  union all
   select 2 as Team, '58' as Minute, 'Harun' as Player)

select 
	T.Team
	, MM.[Minute] as Minutes
	, T.Player 
 from 
	(select T.Team,T.Player
	 from  T
	 group by T.Team,T.Player) as  T
	CROSS APPLY
	(select T2.Minute + ' , '
	  from T as T2
	  where T2.Player=T.Player
	        and T2.Team=T.Team
	  for xml path('')
	) as MM(Minute)


Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 05/14/2013 :  02:16:40  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
Hi,

I need to use the where clause of this query also;

SELECT MatchID FROM [dbo].[LS_A] where SpecialEventCode =1 and StartDate <= GETDATE() and StatusID in (2,3,4,8,9)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/14/2013 :  02:44:13  Show Profile  Reply with Quote

;With CTE
AS
(
SELECT Team, Minute, Player 
FROM [dbo].[B] b
WHERE MatchID =(SELECT MatchID FROM [dbo].[LS_A] where SpecialEventCode =1 and StartDate <= GETDATE() and StatusID in (2,3,4,8,9))
)

SELECT c.Team,
STUFF((SELECT ',' + CAST([Minute] AS varchar(5)) 
FROM CTE 
WHERE Team = c.Team 
AND Player = c.Player 
ORDER BY [Minute] asc
FOR XML PATH('')),1,1,'') AS [Minute],
c.Player
FROM (SELECT DISTINCT Team,Player FROM CTE) c


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 05/14/2013 :  02:49:28  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
visakh16 saved the day again. Thank you. By the way do you have linkedin account? I would like to add you.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/14/2013 :  02:53:45  Show Profile  Reply with Quote
quote:
Originally posted by raysefo

visakh16 saved the day again. Thank you. By the way do you have linkedin account? I would like to add you.


yep I've
http://in.linkedin.com/in/visakhsqlmvp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 05/14/2013 :  02:59:45  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
I sent a friend request. May the query above cause any performance problems?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/14/2013 :  03:04:26  Show Profile  Reply with Quote
quote:
Originally posted by raysefo

I sent a friend request. May the query above cause any performance problems?


Ok...i'll accept when i check it later in the day
Its one of the optimized methods for concatenating column values compared to traditional UDF etc approaches

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 05/14/2013 :  03:07:27  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
thank you for your help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/14/2013 :  03:40:26  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 05/14/2013 :  04:28:44  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
Hi visakh16,

Can I have single quote after minutes as follows??

Team Minute Player
1 44',55' Cenk
2 58' Harun
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/14/2013 :  05:09:14  Show Profile  Reply with Quote
quote:
Originally posted by raysefo

Hi visakh16,

Can I have single quote after minutes as follows??

Team Minute Player
1 44',55' Cenk
2 58' Harun



yep you can
modify like below

;With CTE
AS
(
SELECT Team, Minute, Player 
FROM [dbo].[B] b
WHERE MatchID =(SELECT MatchID FROM [dbo].[LS_A] where SpecialEventCode =1 and StartDate <= GETDATE() and StatusID in (2,3,4,8,9))
)

SELECT c.Team,
STUFF((SELECT ',' + CAST([Minute]  AS varchar(5)) + ''''
FROM CTE 
WHERE Team = c.Team 
AND Player = c.Player 
ORDER BY [Minute] asc
FOR XML PATH('')),1,1,'') AS [Minute],
c.Player
FROM (SELECT DISTINCT Team,Player FROM CTE) c


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 05/14/2013 :  05:23:06  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
thank you again
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/14/2013 :  05:26:33  Show Profile  Reply with Quote
welcome as always

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 05/15/2013 :  04:08:53  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
Hi visakh16,

Now the query does NOT order by Minute asc. Should I add ORDER BY [Minute] asc at the end of the query again?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/15/2013 :  04:11:20  Show Profile  Reply with Quote
you mean ordering inside comma separated list or ordering in final result?

if former, then earlier suggestion is enough.
if latter make query like

;With CTE
AS
(
SELECT Team, Minute, Player 
FROM [dbo].[B] b
WHERE MatchID =(SELECT MatchID FROM [dbo].[LS_A] where SpecialEventCode =1 and StartDate <= GETDATE() and StatusID in (2,3,4,8,9))
)

SELECT c.Team,
STUFF((SELECT ',' + CAST([Minute]  AS varchar(5)) + ''''
FROM CTE 
WHERE Team = c.Team 
AND Player = c.Player 
ORDER BY [Minute] asc
FOR XML PATH('')),1,1,'') AS [Minute],
c.Player
FROM (SELECT DISTINCT Team,Player FROM CTE) c
ORDER BY [Minute]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 05/15/2013 :  04:13:09  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
thank you
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 05/29/2013 :  14:37:21  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
Hi visakh16,

How can I join another table to the query above? This table is same as table B. Is there a way to join this table also?

CREATE TABLE [dbo].[C](
[MatchID] [nvarchar](100) NULL,
[MatchCode] [nvarchar](100) NULL,
[SpecialEventCode] [nvarchar](100) NULL,
[IncidentID] [nvarchar](100) NULL,
[Team] [nvarchar](10) NULL,
[Minute] [nvarchar](10) NULL,
[Score] [nvarchar](50) NULL,
[deleteInfo] [nvarchar](10) NULL,
[Player] [nvarchar](100) NULL
) ON [PRIMARY]

Best Regards.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/30/2013 :  01:20:23  Show Profile  Reply with Quote
do you mean this?


;With CTE
AS
(
SELECT Team, Minute, Player 
FROM [dbo].[B] b
WHERE MatchID =(SELECT MatchID FROM [dbo].[LS_A] where SpecialEventCode =1 and StartDate <= GETDATE() and StatusID in (2,3,4,8,9))
UNION ALL
SELECT Team, Minute, Player 
FROM [dbo].[C] c
WHERE MatchID =(SELECT MatchID FROM [dbo].[LS_A] where SpecialEventCode =1 and StartDate <= GETDATE() and StatusID in (2,3,4,8,9))
)

SELECT c.Team,
STUFF((SELECT ',' + CAST([Minute]  AS varchar(5)) + ''''
FROM CTE 
WHERE Team = c.Team 
AND Player = c.Player 
ORDER BY [Minute] asc
FOR XML PATH('')),1,1,'') AS [Minute],
c.Player
FROM (SELECT DISTINCT Team,Player FROM CTE) c
ORDER BY [Minute]





if C contains the same records as B and if you want unique ones only then use UNION instead of UNION ALL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 05/30/2013 :  01:53:20  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
thank you again.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/30/2013 :  01:56:41  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000