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

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

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

if @SumClassRoom=''
  Set @SumClassRoom=@ClassRoom
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 )
close curs
deallocate curs
You can create an UDF.

      @group VARCHAR(1),
      @date  VARCHAR(20),
      declare @info varchar(8000)      
      set @info = ''

            @info = @info + CONVERT(varchar(8000), IsNull(classroom,'')) + ','
            @group = group
            @date = date
      order by

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

after this you can use the UDF in a query:

      dbo.concat(group, date) as classroom,
      sum(case when period = 'N' then 1 else null end) as [no of nights]
group by

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



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

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.