AlmostThere AlmostThere - 3 months ago 7
SQL Question

Assigning Binary Values to duplicates in SQL

I have the following table

Animal
dog
dog
dog
cat
cat


I want to run a query that exports the following by assigning binary values.

Animal Unique
dog 1
dog 0
dog 0
cat 1
cat 0


I tried using row_number over partition but I need subsequent values to be zero.

I need this as it will be connected to power query and a pivot table in which I want to sum the column so the I see the unique count of animals

Answer

Leaving aside the odd table design, you can use rows preceeding to do this. Basically, if the animal in the current row = the animal in the preceding row, set it to 0, otherwise set it to 1.

    create multiset  volatile table vt (animal varchar(10))
    on commit preserve rows;
    insert into vt values ( 'cat'  );

    select 
    animal,
    case when max(animal) over (partition by animal order by animal 
    rows between 1 preceding and 1 preceding) = animal then 0 else 1 end as test
    from vt order by animal, test desc

Which will give you:

cat 1
cat 0
dog 1
dog 0
dog 0
Comments