Solved

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

Posted on 2006-03-23
219 Views
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
Question by:mmccy

Author Comment

I mean how to do that in SQL statement ?!!!!
thanks !
0

LVL 7

Expert Comment

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

LVL 9

Accepted Solution

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

LVL 75

Expert Comment

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

I recently came across an interesting Question In EE (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_27070749.html?cid=1135#a35876665) and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.