Geek Answers Handbook

SQL Server: summary functionality, you need to collapse the table

I have data in SQL Server in the format below.

-ID ID2 status time -1384904453 417 stop 2013-11-19 23:40:43.000 -1384900211 417 start 2013-11-19 22:30:06.000 -1384822614 417 stop 2013-11-19 00:56:36.000 -1384813810 417 start 2013-11-18 22:30:06.000 -1384561199 417 stop 2013-11-16 00:19:45.000 -1384554623 417 start 2013-11-15 22:30:06.000 -1384475231 417 stop 2013-11-15 00:26:58.000 -1384468224 417 start 2013-11-14 22:30:06.000 -1384388181 417 stop 2013-11-14 00:16:20.000 -1384381807 417 start 2013-11-13 22:30:06.000 -1384300222 417 stop 2013-11-12 23:50:11.000 -1384295414 417 start 2013-11-12 22:30:06.000 -1384218209 417 stop 2013-11-12 01:03:17.000 -1384209015 417 start 2013-11-11 22:30:06.000

I need them to display data in the following format.

 -ID2 start stop -417 2013-11-19 22:30:06.000 2013-11-19 23:40:43.000 -417 2013-11-18 22:30:06.000 2013-11-19 00:56:36.000

Can this be done? I tried to collapse in SQL Server, but it returns only one record. Can anyone help?

+7
sql sql-server subquery pivot correlated-subquery
source share
2 answers

You can use the PIVOT function to get the result, I would just apply the row_number() window function for the data so that you can return multiple rows for each ID2 :

 select id2, start, stop from ( select id2, status, time, row_number() over(partition by status order by time) seq from yourtable ) d pivot ( max(time) for status in (start, stop) ) piv order by start desc;

See SQL Fiddle with Demo .

You can also use the aggregate function with a CASE expression to get the final result:

 select id2, max(case when status = 'start' then time end) start, max(case when status = 'start' then time end) stop from ( select id2, status, time, row_number() over(partition by status order by time) seq from yourtable ) d group by id2, seq;

See SQL script for a demo

+12
source share

You do not need a PIVOT request to get the information you need. You can do the following:

 SELECT mt1.ID2, mt1.time AS start, ( SELECT TOP 1 mt2.time FROM MyTable AS mt2 WHERE mt2.status = 'stop' AND mt2.time >= mt1.time ORDER BY mt2.time ) AS stop FROM MyTable AS mt1 WHERE mt1.status = 'start'

If you execute the above query in SQL Server and not in MS Access, you need to use TOP(1) instead of TOP 1 .

Here is an SQL script demonstrating the above query in SQL Server.

+6
source share

More articles:


All Articles