Jeff Moden (3/4/2012)

---

BWWWAAAA_HAAAA!!!! I know this thread is old but I think it's a funny thread. Let me replay the whole thing for you...

We have a team...

Jeff, personally, you have no...

Could you please post the query with LEFT JOIN you've tried and also add some test data into setup...

Which one? Yours or my one? Compare to what?

My one does...

I think you still can do it with ##table, but you need to make everything in to your dynimc sql string:

DECLARE @sql VARCHAR(8000)

DECLARE @IdNbr VARCHAR(50)

SET @IdNbr = '312012145014'

SET @sql ...

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)

AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],

CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],

CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,

CASE WHEN r.statement_end_offset = -1 THEN 1000...

Nope, you will not.

What you need to do is to have the splitter function.

It may be that I will be killed by the community for the following advice :hehe::

If...

If you go with the route with having permanent table, don't forget to implement some sort of clearing mechanism to keep the table size under control.

Also, as you will...

Looks like OP is happy with his SPACE-noodles and has lost the interest to the thread, while the busy people (us we all are) still in the process of discussion...

ColdCoffee (3/1/2012)

---

Hey R.P.R , the OP does not want the rows that have 12 values , instead the rows that contain the value 12 ; look at the expected output...

Could be easier, but also it may not be the case, depends on real...

Use permanent table instead of ##table, tag each row with UserId and, if you have any, session token.

Use string splitter function (SQL one based on Tally-table is well described in http://www.sqlservercentral.com/articles/Tally+Table/72993/ by Jefff Moden).

As everyone else I would suggest to read through forum etiquette article by Jeff Moden, link also can be found at the bottom of my signature.

Here I try to show...

sotn (2/29/2012)

---

...The logicial reads are the same but the duration on one server is between 50% and 100% slower, from 9 seconds to between 13 and 17 seconds to return...