• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • 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
 
luaniCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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