• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

grouping the results of a few rows in one row !!

I have the resultset like below:

date                                 group                         classroom                        period
1/1-4/1                             A                               A1                                  D
1/1-4/1                             A                               A2                                  N
1/1-4/1                             A                               A3                                  N
1/2-4/2                              B                               B1                                  D
1/2-4/2                              B                                B2                                  N

I want to group the classroom according to the group AND count the no of N (period) for each group,
and display as:
date                                group                         classroom                        no of nights
1/1-4/1                            A                               A1, A2, A3                       2
1/2-4/2                            B                               B1, B2                             1

any idea ?
Thanks !!
Mike
0
mmccy
Asked:
mmccy
1 Solution
 
mmccyAuthor Commented:
I mean how to do that in SQL statement ?!!!!
thanks !
0
 
Luan JubicaProject ManagerCommented:
you can do this:
Select Date ,group,sum(case period when D then 0 else 1 end) from tableName

I don't see any way to sum-up  classroom field but using a cursor.

Declare curs cursor for Select date,group from tablename
Declare @Data as datetime
Declare @Group as varchar(2)
Declare @PrevGroup as varchar(2)
Declare @Classroom as varchar(10)
Declare @Period as varchar(1)
Declare @SumClassRoom as varchar(100)
Declare @SumNight as int
Set @SumNight =0
Set @SumClassRoom=''

Open Curs
Fetch Next from curs into @Data,@Group,@ClassRoom,@Period
set @PrevGroup =@Group
White @@Fetch_Status=0
Begin

if @SumClassRoom=''
  Set @SumClassRoom=@ClassRoom
else
Set @SumClassRoom=@SumClassRoom +',' + @ClassRoom

if @Period ='N' Set @SumNight =@SumNight +1

Fetch Next from curs into @Data,@Group,@ClassRoom,@Period
if @PrevGroup <>@Group insert into #tempTable (Data,Group,ClassRooms,NrofNights) values (@Data, @Group,@SumClassRoom ,@SumNight )
End
close curs
deallocate curs
0
 
auke_tCommented:
You can create an UDF.

CREATE FUNCTION
      [dbo].[Concat]
(
      @group VARCHAR(1),
      @date  VARCHAR(20),
)  
RETURNS VARCHAR(8000) AS  
BEGIN
      declare @info varchar(8000)      
      set @info = ''

      select
            @info = @info + CONVERT(varchar(8000), IsNull(classroom,'')) + ','
      FROM
            yourtable
      where
            @group = group
      and
            @date = date
      order by
            classroom

      return (substring(@info, 1, length(@info)-1))
END

after this you can use the UDF in a query:

select
      date,
      group,
      dbo.concat(group, date) as classroom,
      sum(case when period = 'N' then 1 else null end) as [no of nights]
from
      yourtable
group by
      date,
      group
0
 
Anthony PerkinsCommented:
A slight abbreviated function using auke_t solution (no points, please):

CREATE FUNCTION [dbo].[Concat]
(
     @group VARCHAR(1),
     @date  VARCHAR(20)                 -- Change to datetime if appropriate
)  
RETURNS VARCHAR(8000)

AS  

BEGIN

      declare @info varchar(8000)    
      
      select @info = IsNull(@info + ',', '') + classroom
      FROM yourtable
      where [group] = @group
             and date = @date
      order by  classroom
      
      return @info

END
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now