jazz01



Hi,
I am bit new to sql server. I just want to know is there anything else which i can use instead of CASE statement.
At the moment iam using case it giving the right result but in a new row.
eg
id data data1
1 null 95
1 23 null

instead i want the data only in one row
id data data1
1 23 95


can anyone help me in this....


reg
jazz



Re: using Case with stored procedure

Bryan O


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






Re: using Case with stored procedure

Manivannan.D.Sekaran

Here it is,

Code Snippet

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,

Code Snippet

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,

Code Snippet

select

id

,[1] as [data]

,[2] as [data1]

from

#data

Pivot

(

sum([value]) for [type] in ([1],[2])

) as pvt