I think this what you want:
DECLARE @blockinfo TABLE
(
spid INT ,
waitresource VARCHAR(100) ,
typ VARCHAR(25) ,
ecid INT ,
blocked INT ,
lastwaittype VARCHAR(100) ,
login_time DATETIME ,
open_tran INT ,
cmd VARCHAR(100)
);
INSERT INTO @blockinfo
( spid, waitresource, typ, ecid, blocked, lastwaittype, login_time,
open_tran, cmd )
VALUES ( 54, '', 'Blocking', 0, 0, 'MISCELLANEOUS',
' 2014-03-07 11:27:08.743', 2, 'AWAITING COMMAND' ),
( 57, 'RID: 11:1:2937:8', 'Blocking', 0, 54, 'LCK_M_U',
' 2014-03-07 11:27:34.197', 2, 'UPDATE' ),
( 57, 'RID: 11:1:2937:8', 'Blocked', 0, 54, 'LCK_M_U',
'2014-03-07 11:27:34.197', 2, 'UPDATE' ),
( 59, 'RID: 11:1:2937:8', 'Blocked', 0, 57, 'LCK_M_U',
' 2014-03-07 11:27:38.457', 2, 'UPDATE' ),
( 56, 'RID: 11:1:2937:8', 'Blocking', 0, 57, 'LCK_M_U',
' 2014-03-07 11:36:27.660', 2, 'DELETE' ),
( 56, 'RID: 11:1:2937:8', 'Blocked', 0, 57, 'LCK_M_U',
' 2014-03-07 11:36:27.660', 2, 'DELETE' ),
( 55, 'RID: 11:1:2937:0', 'Blocked', 0, 56, 'LCK_M_S',
' 2014-03-07 11:49:10.520', 0, 'SELECT' );
WITH BlockingBehaviour
AS ( SELECT -- distinct
blocking.[spid] ,
blocking.[waitresource] ,
blocking.[typ] ,
blocking.[blocked] ,
blocking.[lastwaittype] ,
blocking.[login_time] ,
blocking.[open_tran] ,
blocking.[cmd] ,
1 AS block_level
FROM @blockinfo AS blocking
WHERE blocked = 0
UNION ALL
SELECT --distinct
blocked.[spid] ,
blocked.[waitresource] ,
blocked.[typ] ,
blocked.[blocked] ,
blocked.[lastwaittype] ,
blocked.[login_time] ,
blocked.[open_tran] ,
blocked.[cmd] ,
BB.block_level + 1
FROM @blockinfo AS blocked
INNER JOIN BlockingBehaviour AS BB ON blocked.blocked = bb.spid
)
SELECT DISTINCT
spid ,
waitresource ,
typ ,
blocked ,
lastwaittype ,
login_time ,
open_tran ,
cmd ,
block_level
FROM BlockingBehaviour
ORDER BY block_level ASC ,
typ DESC;
Notice I took the data you gave and put it in a table so I could provide a tested solution. I also think it would be more clear if the blocked column was named either blocked_by or blocker.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question