CODEWITHSUNDEEP
sqlsql-serverdatabasestored-procedures
techV
techV

Reputation: 935

How to get only specified values record from multiple values of one Record in sql server

i have a table 'DEMO' like below, I need some specified result in my select query

 PID   Is_Viewed
  1      False          
  1      False     
  1      False      
  1      False  
  2      True
  2      False

Now i need to select only those record which if having even a single value 'True' then result will be 1 else 0 in select statment. i have tried much but not getting the desired result... my query is 

/// select statement
    SELECT distinct PId,CASE WHEN EXISTS(Select Is_Viewed from DEMO where Is_viewed=1) then 1 else 0 end as Is_viewed FROM DEMO


    i WANT result like

    PId     Is_Viewed
    1        0
    2        1

Upvotes: 2

Views: 90

Answers (3)

mohan111
mohan111

Reputation: 8865

DECLARE @tbl TABLE(PID INT,Is_Viewed VARCHAR(10))
INSERT INTO @tbl
VALUES
(1,'False'),          
(1,'False'),  
(1,'False'),      
(1,'False'),  
(2,'True'),
(2,'False')



select distinct t.PID,
    CASE WHEN t.Is_Viewed = 'TRUE' 
        THEN 1 ELSE 0 END As PType from @tbl t
    INNER JOIN 
        (Select MIN(PID)As PID,Is_Viewed 
                FROM @tbl
GROUP BY  Is_Viewed)AS KK
ON KK.PID = t.PID
AND KK.Is_Viewed = t.Is_Viewed

Upvotes: 0

valex
valex

Reputation: 24144

I guess that Is_Viewed is defined as a bit type since SQL server doesn't have a BOOLEAN type. Then try this:

SELECT PID,
       MAX(CAST(Is_Viewed as INT)) as Is_Viewed 
FROM T
GROUP BY PID

SQLFiddle demo

Upvotes: 0

Arion
Arion

Reputation: 31239

Maybe something like this:

SELECT
    PId,
    MAX(CASE WHEN Is_Viewed='True' THEN 1 ELSE 0 END) AS Is_Viewed
FROM
    DEMO
GROUP BY
    PId

Edit

Considering the data that you have supplied.

DECLARE @tbl TABLE(PID INT,Is_Viewed VARCHAR(10))
INSERT INTO @tbl
VALUES
(1,'False'),          
(1,'False'),  
(1,'False'),      
(1,'False'),  
(2,'True'),
(2,'False')

With this query

SELECT
    PId,
    MAX(CASE WHEN Is_Viewed='True' THEN 1 ELSE 0 END) AS Is_Viewed
FROM
    @tbl AS tbl
GROUP BY
    PId

The output is this:

PId     Is_Viewed
1       0
2       1

Upvotes: 3

Related Questions