i want to select row on condition based on column values in sql server please check below example with required result.
WITH allData
AS (
select mlid=1,value=0,checkid=1
union all
select mlid=2,value=6,checkid=2
union all
select mlid=3,value=6,checkid=1
union all
select mlid=4,value=0,checkid=2
)
select * from allData
Result
Mlid Value checked
1 0 1
2 6 2
3 6 1
4 0 2
required result -->
condition:- if checked column values is 1 and values column is 0 than display checked values values 2 rows only
either display checked column values 1 like below result
Mlid value checked
2 6 2
3 6 1
This will work for your sample data, but would fail for pretty much anything else?
WITH allData AS (
SELECT MLID = 1, [VALUE] = 0, CHECKID = 1
UNION ALL
SELECT MLID = 2, [VALUE] = 6, CHECKID = 2
UNION ALL
SELECT MLID = 3, [VALUE] = 6, CHECKID = 1
UNION ALL
SELECT MLID = 4, [VALUE] = 0, CHECKID = 2)
SELECT
CASE WHEN a1.CHECKID = 1 AND a1.VALUE = 0 THEN a2.MLID ELSE a1.MLID END AS MLID,
CASE WHEN a1.CHECKID = 1 AND a1.VALUE = 0 THEN a2.[VALUE] ELSE a1.[VALUE] END AS [VALUE],
CASE WHEN a1.CHECKID = 1 AND a1.VALUE = 0 THEN a2.CHECKID ELSE a1.CHECKID END AS CHECKID
FROM
allData a1
INNER JOIN allData a2 ON a2.MLID = a1.MLID + 1 AND a2.CHECKID = 2
WHERE
a1.CHECKID = 1;
I guess this might get you started on a better query, or even raise some questions about what you actually need, and how these rows are related?
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments