hw to write sql conditions in select query

Posted by Oswaldlily under Sql Server on 3/10/2014 | Points: 10 | Views : 1497 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

CardNo type
1000 X
2000 X
3000 Y
500 Y

if type='X' then sum(CardNO)
else
cardNo

expected o/p
CardNo type
3000 X
3000 Y
500 Y

How to write this in single select query..
where only row by row gets checked..

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 3/10/2014 [Member] [MVP] Platinum | Points: 25

0	SELECT distinct CASE WHEN type = 'Y' THEN cardNo ELSE SUM(case when type = 'X' then Cardno END) OVER() end ,type FROM TableName Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Oswaldlily, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 3/10/2014 [Member] [MVP] Platinum | Points: 25

0	hi Vijila, If you get solution from posted responses please mark those responses as answer by clicking on "Mark this response as answer " link Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Oswaldlily, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Brahmam on: 3/11/2014 [Member] Starter | Points: 25

0	select distinct cardno=(CASE type WHEN 'x' THEN SUM(case when type = 'X' then Cardno END) OVER() else cardNo END),type from card brahmam Oswaldlily, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Saranpselvam on: 4/4/2014 [Member] Starter | Points: 25

0	-- 1 create table test3 (number int,cosde varchar(10)) -- 2 insert into test3 select 1000,'X' union select 4000,'X' union select 3000,'Y' union select 1000,'Y' union select 5000,'z' -- 3 select * from test3 -- 4 select distinct cosde, case when cosde ='X' then sum(case when cosde ='X' then number end) OVER() else number end from test3 Oswaldlily, if this helps please login to Mark As Answer. \| Alert Moderator

-- 1
create table test3 (number int,cosde varchar(10))
-- 2
insert into test3
select 1000,'X'
union
select 4000,'X'
union
select 3000,'Y'
union
select 1000,'Y'
union
select 5000,'z'

-- 3
select * from test3

-- 4
select distinct cosde,
case when cosde ='X' then sum(case when cosde ='X' then number end) OVER() else number end
from test3

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts