SQL

[Tank50]

Hi

I need a help in SQL,There is table called sales.Its contains the day,sales quantity.If I write sql like below

select day,Sales_quantity from Sales.

Then result should be
Day SalesQuantity
Monday 20
Monday 302
Monday 50
Tuseday 30
etc..

The problem is I want to get result like below

Day SalesQuantity
Monday 20,302,50
Tuesday 30

There should be ',' sign between two values.How Do I write this kind of SQL.

Thanks
Tank50

[huangzhi]

I think you cannot use simple sql command to get result like that. You can use cursor to concatenate the sales quantity like below:

declare @Day varchar(255), @Sales int

create table #tmpSales ([Day] varchar(255), SalesQuantity int)

insert #tmpSales
  select 'Monday', 20	union all
  select 'Monday', 302	union all
  select 'Monday', 50	union all
  select 'Tuseday', 30

select distinct [Day], cast('' as varchar(1024)) as strSalesQuantity
  into #tmpResult
  from #tmpSales

declare cs_Day cursor forward_only for select [Day] from #tmpResult
open cs_Day

fetch next from cs_Day into @Day
while @@fetch_status = 0
begin
  declare cs_Sales cursor forward_only for select SalesQuantity from #tmpSales where [Day] = @Day
  open cs_Sales
  fetch next from cs_Sales into @Sales
  while @@fetch_status = 0
  begin
    update #tmpResult set strSalesQuantity = strSalesQuantity + case when strSalesQuantity <> '' then ',' else '' end + cast(@Sales as varchar) where [Day] = @Day
    fetch next from cs_Sales into @Sales  
  end
  close cs_Sales
  deallocate cs_Sales

  fetch next from cs_Day into @Day
end

close cs_Day
deallocate cs_Day
select * from #tmpResult

drop table #tmpResult
drop table #tmpSales

[huangzhi]

or use these commands:

declare @Day varchar(255), @Sales int, @strSales varchar(1024)

CREATE TABLE #tmpSales ([Day] varchar(255), SalesQuantity int)
INSERT #tmpSales
  SELECT 'Monday', 20 union ALL
  SELECT 'Monday', 302 union ALL
  SELECT 'Monday', 50 union ALL
  SELECT 'Tuseday', 30

SELECT DISTINCT [Day], cast('' AS varchar(1024)) AS strSalesQuantity
  INTO #tmpResult
  FROM #tmpSales

declare cs_Day cursor forward_only FOR SELECT [Day] FROM #tmpResult
open cs_Day
fetch next FROM cs_Day INTO @Day
while @@fetch_status = 0
begin
  select @strSales = ''
  select @strSales = @strSales + case when @strSales <> '' then ',' else '' end + cast(SalesQuantity as varchar)
    from #tmpSales 
    where [Day] = @Day
  UPDATE #tmpResult set strSalesQuantity = @strSales where [Day] = @Day

  fetch next FROM cs_Day INTO @Day
end
close cs_Day
deallocate cs_Day

SELECT * FROM #tmpResult

DROP TABLE #tmpResult
DROP TABLE #tmpSales