I want to return a data set that co

[Larrabbb]

I am using SQL Server 2000, and I want to return a data set that consists of the following

Chan1 Chan2
Apr 116 1
May 112 5

Here is the aggregate table that I am pulling from:

CatID Channel mth Disp_mth yr vol
3502 Chan1 4 April 2003 116
3502 Chan2 4 April 2003 1
3502 Chan1 4 May 2003 112
3502 Chan2 4 May 2003 5

Additionally, for any given month, there may be any number of items. So the data set that is returned may look like this:

Chan1 Chan2 Chan3 Chan4 Chan5 Chan ‘n’
Apr 116 1 10 12 22 n
May 112 5 13 5 6 n

Any thoughts on this? I have more information if needed.

 

[tlbroadbent]

Do a keyword search of this forum for "crosstab" or "cross tab." There are several threads where you can find various approaches to creating crosstab query results. You'll need to create a dynamic crosstab if the number of entries varies.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[ClaireHCM]

/*You can seperate it into two step!
Here I name the new table as "Result"*/
--(1) create the table with the form you desire!

create table Result(disp_mth varchar(10))
declare @id int
declare @string varchar(50)
set @id = 1
while @id <=(select max(convert(int,right(channel,len(channel)-4))) from table11)
begin
set @string = &quot;alter table Result add chan&quot;+convert(varchar(5),@id)+&quot; int&quot;
exec(@string)
set @id = @id+1
end

/*Now insert the values by using crosstab*/

select Disp_mth,
SUM(CASE WHEN disp_mth =disp_mth and channel = 'chan1' THEN vol END),
SUM(CASE WHEN disp_mth =disp_mth and channel = 'chan2' THEN vol END),
SUM(CASE WHEN disp_mth =disp_mth and channel = 'chan3' THEN vol END),
SUM(CASE WHEN disp_mth =disp_mth and channel = 'chan4' THEN vol END),
SUM(CASE WHEN disp_mth =disp_mth and channel = 'chan5' THEN vol END)
from table11 group by disp_mth

claire