SQL

Expand Post »

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

Tank50

Reputation Points: 12

Solved Threads: 1

Junior Poster

Offline

124 posts
since Aug 2008

Permalink

Nov 15th, 2009

Re: SQL

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

ms sql Syntax (Toggle Plain Text)
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
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

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

Permalink

Nov 16th, 2009

Re: SQL

or use these commands:

ms sql Syntax (Toggle Plain Text)
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
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

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: SQL SUM Decimals

Next Thread in MS SQL Forum Timeline: My Insert statement needs a trim

DaniWeb Message

Cancel Changes

User Name
Password