Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Create columns

Answered Create columns

  • Thursday, February 14, 2013 7:26 AM
     
     
    Sign In to Vote
    0

    Hello,

    I have table which have ID  int  column which is containing values  as

    ID

    1     

    1

    1

    2

    3

    3

    4

    5

    5

    6

    7

    Now I want  new column  value with following  data

    Means where the ID value is not distinct put 0 in new column

    ID          Value

    1               0

    1               0

    1               0

    2               1

    3               0

    3               0

    4               1

    5               1

    5               1

    6               1

    7              1



     

All Replies

  • Thursday, February 14, 2013 7:43 AM
     
     Answered
    Sign In to Vote
    2

    Hi Sanjay,

    You can use the below query to achive your Requirement

    Select a.id,b.value from Test a inner join (
    Select ID, CASE WHEN Count(*) >1 THEN '0' ELSE '1' END value from Test
    group by ID) b on a.id = b.id

    Regards

    Naveen

    • Marked As Answer by Sanjay_S Thursday, February 14, 2013 11:29 AM
    •  
     
  • Thursday, February 14, 2013 7:43 AM
     
     Answered
    Sign In to Vote
    2

    Try

    with cte as
    (select id,count(*) cnt
    from tab1
    group by id)
    select a.id,case when cnt=1 then 1 else 0 end Value
    from tab1 a, cte b
    where a.id=b.id;

    Many Thanks & Best Regards, Hua Min


     
  • Thursday, February 14, 2013 7:44 AM
     
     Answered Has Code
    Sign In to Vote
    2

    In your desired result, you show the value for the two rows with  ID = 5 to be set to 1.  I'm guessing that you meant for those to be set to 0?.  If so, then you can do

    ;With cte As
(Select ID, Count(*) As NbrRows
From YourTable
Group By ID)
Update y
Set Value = Case When c.NbrRows = 1 Then 1 Else 0 End
From YourTable y
Inner Join cte c On y.ID = c.ID;

    Tom

     
  • Thursday, February 14, 2013 11:30 AM
     
     
    Sign In to Vote
    0
    Thank you all