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
mmccyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mmccyAuthor Commented:
I mean how to do that in SQL statement ?!!!!
thanks !
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.