Transact-SQL
Without knowing more about what you're trying to accomplish and assuming you have only one non-null value for any column for any given ID, this would work:
SELECT ID, SUM(data) AS data, SUM(data1) AS data1
FROM dbo.jazztable
GROUP BY ID
Here it is,
create table #data ( [id] int , [type] varchar(100) , [value] int ); insert into #data values('1','1','23'); insert into #data values('1','2','95'); /* --your query select id ,case when [type]=1 then [value] end as [data] ,case when [type]=2 then [value] end as [data1] from #data */
On SQL Server 2000 & 2005,
select id ,sum(case when [type]=1 then [value] end) as [data] ,sum(case when [type]=2 then [value] end) as [data1] from #data group by id
On SQL Server 2005,
select id ,[1] as [data] ,[2] as [data1] from #data Pivot ( sum([value]) for [type] in ([1],[2]) ) as pvt