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 2008 Forums

Transact-SQL (2008)

Group By Question?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2013-05-13 : 09:26:31

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
Aged Yak Warrior

545 Posts

Posted - 2013-05-13 : 09:59:36

[code]

;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)
[/code]

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2013-05-14 : 02:16:40

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 02:44:13

[code]
;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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2013-05-14 : 02:49:28

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 02:53:45

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

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2013-05-14 : 02:59:45

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 03:04:26

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

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2013-05-14 : 03:07:27

thank you for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 03:40:26

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2013-05-14 : 04:28:44

Hi visakh16,

Can I have single quote after minutes as follows??

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 05:09:14

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

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2013-05-14 : 05:23:06

thank you again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 05:26:33

welcome as always

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2013-05-15 : 04:08:53

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 04:11:20

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

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2013-05-15 : 04:13:09

thank you

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2013-05-29 : 14:37:21

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 01:20:23

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

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2013-05-30 : 01:53:20

thank you again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 01:56:41

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources