March 3, 2015 at 3:00 pm
hi,
I want to check if column 1 = 'ab' then I need all the other columns (column2, column3 ) values need to be zero
I tried doing this
select case when column1 = 'ab' then column2 = 0
when column1 = 'ab' then column3 = 0
But this is not the write way doing it, Can one give any inputs, please.
March 3, 2015 at 3:38 pm
Sangeeth878787 (3/3/2015)
hi,I want to check if column 1 = 'ab' then I need all the other columns (column2, column3 ) values need to be zero
I tried doing this
select case when column1 = 'ab' then column2 = 0
when column1 = 'ab' then column3 = 0
But this is not the write way doing it, Can one give any inputs, please.
How about a create table statement, some sample data and sample expected outputs? From what you wrote you want your statement is correct I think, although it is missing an ELSE for when column1 is NOT = 'ab'.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 3, 2015 at 6:37 pm
Quick note, each case statement can only output to a single column, consider this example
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_DATA TABLE
(
COL_1 INT NOT NULL
,COL_2 INT NOT NULL
,COL_3 INT NOT NULL
);
INSERT INTO @SAMPLE_DATA(COL_1,COL_2,COL_3)
SELECT TOP(10)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 3
,ABS(CHECKSUM(NEWID())) % 100
,ABS(CHECKSUM(NEWID())) % 500
FROM sys.all_columns;
SELECT
SD.COL_1
,CASE
WHEN SD.COL_1 = 1 THEN 0
ELSE SD.COL_2
END AS COL_2
,CASE
WHEN SD.COL_1 = 1 THEN 0
ELSE SD.COL_3
END AS COL_3
FROM @SAMPLE_DATA SD
;
Sample output
COL_1 COL_2 COL_3
----------- ----------- -----------
1 0 0
2 52 389
0 99 432
1 0 0
2 64 481
0 4 284
1 0 0
2 54 457
0 87 190
1 0 0
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply