February 4, 2005 at 5:39 am
Hi Gurus,
I have a table with values. The table will keep on increasing with more values.
id value
1 100
1 200
2 50
2 100
3 300
3 200
I wanna define a query for which the output should be :
1 2 3
300 150 500
I could only think of :
select id,sum(value) as val from sa group by id.
(This gives result in culmnar format and not row format)
Please help me in this.
Thanks in advance....Kishore
February 4, 2005 at 6:45 am
How about something like this:
___________________________________________________________
declare @@counter integer
declare @@appendsql varchar(1000)
declare @@sql varchar(1000)
set @@counter = 1
set @@sql = 'select '
WHILE @@counter < (select COUNT(distinct ID) from temp)
BEGIN
set @@appendsql = 'SUM(CASE ID WHEN ' + CAST(@@counter as varchar(1000)) +
' THEN value ELSE 0 END) as ID' + CAST(@@counter as varchar(1000)) + ','
set @@sql = @@sql + @@appendsql
set @@counter = @@counter+1
END
set @@appendsql = 'SUM(CASE ID WHEN ' + CAST(@@counter as varchar(1000)) +
' THEN value ELSE 0 END) as ID' + CAST(@@counter as varchar(1000))
set @@sql = @@sql + @@appendsql + ' from temp'
exec(@@sql)
____________________________________________________________
Ryan
February 4, 2005 at 7:39 am
Kishore - I made a newbie mistake. Most of the execution time is spent keeping track of the counter. If you have already copied the code, make sure to make this change:
Change:
WHILE @@counter < (select COUNT(distinct ID) from temp)
TO:
declare @@boundry integer
set @@boundry = (select COUNT(distinct ID) from temp)
WHILE @@counter < @@boundry
February 4, 2005 at 7:42 am
Hi Kishore
Newbie query is not working for the below values.. updated one for you
create table sa
(
id int,
value int)
insert sa values(1,100)
insert sa values(1,200)
insert sa values(2,50)
insert sa values(2,100)
insert sa values(3,300)
insert sa values(3,200)
insert sa values(4,300)
insert sa values(4,200)
insert sa values(5,300)
insert sa values(5,200)
insert sa values(15,300)
insert sa values(15,200)
declare @appendsql varchar(1000)
declare @sql varchar(1000)
declare @ID numeric(28,0)
set @sql = 'select '
DECLARE Count_Value CURSOR FOR select distinct ID from sa
OPEN Count_Value
FETCH NEXT FROM Count_Value INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
set @appendsql = 'SUM(CASE ID WHEN ' + CAST(@ID as varchar(1000)) +
' THEN value ELSE 0 END) as ''' + CAST(@ID as varchar(1000)) + ''','
set @sql = @sql + @appendsql
FETCH NEXT FROM Count_Value INTO @ID
END
CLOSE Count_Value
DEALLOCATE Count_Value
set @sql = substring(@sql,0,len(@sql))
set @sql = @sql + ' from sa '
exec(@sql)
With Regards
MeenakshiSundaram Lakshmanan
February 4, 2005 at 7:54 am
Are you saying my solution won't work with the values below?
insert sa values(1,100)
insert sa values(1,200)
insert sa values(2,50)
insert sa values(2,100)
insert sa values(3,300)
insert sa values(3,200)
insert sa values(4,300)
insert sa values(4,200)
insert sa values(5,300)
insert sa values(5,200)
insert sa values(15,300)
insert sa values(15,200)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply