SQL Checking if next value is the same or different and write only if it is different

Question

I am working on sql (TSQL) query to be used for a report (ssrs). I have a table of a device's values as below (of course there are other fields but these two are important)

 device| value
  01      a
  01      a
  01      b
  01      a
  01      b
  01      c
  01      c
  01      c
  01      d

And what I would like is to see only different rows but not like distinct but rather like

 device| value
  01      a
  01      b
  01      a
  01      b
  01      c
  01      d

And after that I would write for every user something like path of values using path for xml

device |  path 
 01      a - b - a - b - c - d

And after that group by path and count number of users

Do you think this is possible?

Answer 1

You can use the LAG function.

SELECT 
 ... 
 , LAG(ValueYouWishToUse, 1,0) OVER (ORDER BY YEAR(OrderByColumnName)) AS PreviousValue
FROM
 ...
WHERE
 ...

Please refer to this for more information.