View of Adjacent Pairs Segmented by Group

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

View of Adjacent Pairs Segmented by Group

Author

Topic

ManiacalLoon
Starting Member

3 Posts

Posted - 2011-09-28 : 00:16:44

Hello. I'm a bit stuck trying to build a view in SQL Server 2008R2.

I have a table that's a log of sorts, logging inputs and responses by user and by time. Something along the lines of this:

CREATE TABLE Entries (
	userID int NOT NULL,
	entryID int NOT NULL,
	requested_dt datetime NOT NULL,
	request varchar(255) NULL,
	response varchar(255) NULL)

INSERT INTO Entries (userID, entryID, requested_dt, request, response)
SELECT 12582, 154, '2011-08-01 00:01:22.000', 'catfish?', 'No. Go Fish' UNION ALL
SELECT 12597, 162, '2011-08-01 00:01:53.000', 'Play a game', 'You can start' UNION ALL
SELECT 12582, 164, '2011-08-01 00:02:01.000', 'ok. Grouper?', 'No. Go Fish.' UNION ALL
SELECT 12597, 165, '2011-08-01 00:02:14.000', 'ok. dogfish?', 'No. Go Fish' UNION ALL
SELECT 12582, 167, '2011-08-01 00:02:22.000', 'I give up.', 'You lose.' UNION ALL
SELECT 12597, 170, '2011-08-01 00:03:47.000', 'clown fish.', 'Yes! You win.'

I want to generate a view that shows multiple columns for "pairs" of entries. Think of it as putting two copies of the table side by side, sorting by user and datetime, and then offsetting one of the tables by a row.

I would expect the result to be something like this:

UserID	Request1_dt		Request1	Response1	Request2	Response2
12582	2011-08-01 00:01:22.000	catfish?	No. Go Fish.	ok. Grouper?	No. Go Fish
12582	2011-08-01 00:02:01.000	ok. Grouper?	No. Go Fish.	I give up.	You lose.
12582	2011-08-01 00:02:22.000	I give up.	You lose.	NULL	NULL
12597	2011-08-01 00:02:33.000	Play a game	You can start.	ok. dogfish?	No. Go Fish
12597	2011-08-01 00:03:14.000	ok. dogfish?	No. Go Fish.	clown fish.	Yes! You win.
12597	2011-08-01 00:03:47.000	clown fish.	Yes! You win.	NULL		NULL

I am able to get a single column from the "next" record for each entry via correlated subquery but I can't figure out how to grab multiple columns from the subsequent entries. I know the concept is a bit strange. Think of it as a sort of path analysis exercise. Ultimately, I'd like to have data arranged such that I can (via SQL or Excel) view the responses and/or requests that either precede or follow any given response.

Clear as mud?

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 00:30:51

so what should be your output for the sample data above?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-28 : 01:19:27

I think you have shown us the "expected output" in your question! If so, may be you gotta give us some sample data as well do understand from what data you wanted this output.

Best Regards
Vadivel

http://vadivel.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 01:34:37

oh ok.. I was confused

I think what you're after is this


SELECT e.UserID,
e.Requested_dt,
e.Request AS Request1,
e.Response AS Response1,
e1.Request AS Request2,
e1.Response AS Response2 
FROM Entries e
OUTER APPLY (SELECT TOP 1 request,response
             FROM Entries
             WHERE  UserID = e.UserID	
             AND requested_dt> e.requested_dt
             ORDER BY requested_dt ASC) e1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ManiacalLoon
Starting Member

3 Posts

Posted - 2011-09-28 : 09:46:36

Sorry for the confusion...yes, I just showed the sample data in the expected output. Edited to show it separately.

At first glance, that query looks like what I'm after. I'll do some toying with it this morning and report back. Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 10:41:42

ok...welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ManiacalLoon
Starting Member

3 Posts

Posted - 2011-09-28 : 11:08:53

Worked beautifully. I read up on APPLY on your blog as well. Many thanks for the quick response!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 11:11:41

welcome

Glad that I could be of help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources