sql server - Return a column to notify row change of another column -

i have table:

|----fruit----| |-------------| |--apples--| |--apples--| |--apples--| |--apples--| |-bananas-| |-bananas-| |-oranges-| |--plums---| |--plums---| 

i have below script:

case when [fruit] = [fruit] '0' else [fruit] end 

what want return 2 columns. 1 fruit column , 2nd column shows when fruit column changes next fruit, have following:-

|----fruit----||fruit change| |-------------||----------------| |--apples--||-------0-------| |--apples--||-------0-------| |--apples--||-------0-------| |--apples--||-------0-------| |-bananas-||--bananas--| |-bananas-||-------0-------| |-oranges-||---oranges---| |--plums---||----plums----| |--plums---||-------0-------| |--plums---||-------0-------| |--plums---||-------0-------| |--plums---||-------0-------| |--mango---||---mango---| |--mango---||-------0-------| 

how return column corresponding change in fruit script above doesn't allow me specify need do.

you can use lag below:

select fruit,case when fruit <> lag(fruit) over(order id) fruit else '0' end fruitchange      #data 

output below:

+---------+-------------+ |  fruit  | fruitchange | +---------+-------------+ | apples  | 0           | | apples  | 0           | | apples  | 0           | | apples  | 0           | | bananas | bananas     | | bananas | 0           | | oranges | oranges     | | plums   | plums       | | plums   | 0           | +---------+-------------+