MS SQL

SQL

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Aug 2008

Posts: 100

Reputation: Tank50 is an unknown quantity at this point

Solved Threads: 1

Tank50

Offline

Junior Poster

SQL

34 Days Ago

Hi

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

Help with Code Tags

MS SQL Syntax (Toggle Plain Text)

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

•

Join Date: Feb 2008

Posts: 42

Reputation: huangzhi is an unknown quantity at this point

Solved Threads: 13

huangzhi

Offline

Light Poster

29 Days Ago

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

 Help with Code Tags 
 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

Hence Wijaya
www.ex-Soft.tk

•

Join Date: Feb 2008

Posts: 42

Reputation: huangzhi is an unknown quantity at this point

Solved Threads: 13

huangzhi

Offline

Light Poster

29 Days Ago

or use these commands:

 Help with Code Tags 
 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