Grouping and crosstab like behaviour.... hairy query

Question

Post reply

Grouping and crosstab like behaviour.... hairy query

ZA_Crafty

Ten Centuries

Points: 1228
More actions
February 24, 2011 at 3:55 am

#234263

ok where to begin.
I've got an excel file with a part number, and a factor
I need to change this list into ranges for each factor.
Each factor can have multiple ranges.
This is more or less what I have:
Create Table XlsDump
(PartNumber varchar(50)
,Factor decimal(18,10))
Insert Into XlsDump
Select '35100-07g00-019' as 'PartNumber', '0.2129380000' as 'Factor'
Union Select '35100-07g01-019', '0.2129380000'
Union Select '35100-07g02-019', '0.2129380000'
Union Select '65310-07G01-000', '0.2129380000'
Union Select '65310-07G02-000', '0.2129380000'
Union Select '65310-07G03-000', '0.2129380000'
Union Select '11400-95855-000', '0.2293830000'
Union Select '11400-95856-000', '0.2293830000'
Union Select '11400-95857-000', '0.2293830000'
Union Select '61000-00001-000', '0.2293830000'
Union Select '61000-00002-000', '0.2293830000'
Union Select '61000-00003-000', '0.2293830000'
Union Select '24356-89000-000', '0.3344860000'
Union Select '24356-89001-000', '0.3344860000'
Union Select '24356-89002-000', '0.3344860000'
Union Select '51400-00001-000', '0.3344860000'
Union Select '51400-00002-000', '0.3344860000'
Union Select '51400-00003-000', '0.3344860000'
Select * from XlsDump
And I should somehow build this up to be:
--Select '65310-07G00-000' as 'RangeFrom','65310-07G10-000' as 'RangeTo','0.2129380000' as 'Factor'
--Thanks for highlighting the mistake
Select '65310-07G00-000' as 'RangeFrom','65310-07G03-000' as 'RangeTo','0.2129380000' as 'Factor'
UNION Select '35100-07g00-019','35100-07G10-YU1','0.2129380000'
UNION Select '11400-95855-000','11400-95855-000','0.2293830000'
UNION Select '61000-00000-000','61000-99999-000','0.2293830000'
UNION Select '24356-89000-000','24356-89131-000','0.3344860000'
UNION Select '51400-00000-000','51400-49999.999','0.3344860000'
I started to do this in excel. I Sorted by PartNumber, then by Factor
Manually this is going to take forever!
The XlsDump table has 56K lines
It should resolve to about 100 lines with ranges for the 5 unique factors.
How on earth to I tackle this?
The server is 2000, but for doing to formatting I have a 2008 express running on my pc, so can use advanced functions as well if need be.
Any help will be appreciated.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Jeff Moden SSC Guru Points: 1004548 More actions · Answer 1

Your example output has the following in it...

Select '65310-07G00-000' as 'RangeFrom', '65310-07G10-000' as 'RangeTo', '0.2129380000' as 'Factor'

The largest value for 65310 in your example data is '65310-07G03-000'. Where did you get '65310-07G10-000' from in your example output?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ZA_Crafty Ten Centuries Points: 1228 More actions · Answer 2

Sorry, just noticed that. That was a bit of actual data that got mixed up in sample data.

Thus the output should be:

SELECT '0.2293830000' As 'Factor'

,'11400-95855-000' as 'PartFrom'

,'11400-95857-000' as 'PartTo'

UNION SELECT '0.3344860000' As 'Factor'

,'24356-89000-000' as 'PartFrom'

,'24356-89002-000' as 'PartTo'

UNION SELECT '0.2129380000' As 'Factor'

,'35100-07g00-019' as 'PartFrom'

,'35100-07g02-019' as 'PartTo'

UNION SELECT '0.3344860000' As 'Factor'

,'51400-00001-000' as 'PartFrom'

,'51400-00003-000' as 'PartTo'

UNION SELECT '0.2293830000' As 'Factor'

,'61000-00001-000' as 'PartFrom'

,'61000-00003-000' as 'PartTo'

UNION SELECT '0.2129380000' As 'Factor'

,'65310-07G01-000' as 'PartFrom'

,'65310-07G03-000' as 'PartTo'

ZA_Crafty Ten Centuries Points: 1228 More actions · Answer 3

I have a solution in mind, but would like to see first if there is an alternative.

I can add an extra column to the table. say (seq int null)

Then Create a cursor that compares the current factor in the cursor with the previous factor

If they are the same the seq number stays the same. If not, it is a new range, and I increment seq with one

Then I can pretty much select min(id) and max(id) for that seq to get the partfrom and partto

But I understand this type of thinking is not very popular around here ;-p

ZA_Crafty Ten Centuries Points: 1228 More actions · Answer 4

Ok this is what I did.

Let see if we can find some cleaner solutions.

I for one still use cursors in these type of scenarios, but only because I'm not aware of any other way..Here goes.

--Begin Transaction

declare @OldFactor as decimal(18,10)

declare @NewFactor as decimal(18,10)

declare @part as varchar(50)

declare @ID as int

Declare @Seq as int

Select @Seq = 0

Declare @STR varchar(100)

declare Factors2Process

cursor Static for

(Select PartNumber, Factor, SymFactorID from SymFactor)

open Factors2Process

fetch Next

From Factors2Process

Into @Part, @NewFactor, @ID

While @@fetch_status = 0

begin

--print @ID

if @NewFactor = @OldFactor

Begin

Select @STR = 'Part: ' + cast(isnull(@Part,0) as varchar(50)) + ' New: ' + cast(isnull(@NewFactor,0) as varchar(50)) + ' ' + ' Old: ' + cast(isnull(@OldFactor,0) as varchar(50)) + ' Seq: ' + cast(isnull(@seq,0) as varchar(50))

Print @STR

End

Else

Begin

Select @seq = @seq + 1

Select @STR = 'Part: ' + cast(isnull(@Part,0) as varchar(50)) + ' New: ' + cast(isnull(@NewFactor,0) as varchar(50)) + ' ' + ' Old: ' + cast(isnull(@OldFactor,0) as varchar(50)) + ' Seq: ' + cast(isnull(@seq,0) as varchar(50))

Print @STR

End

update SymFactor2

Set Seq = @Seq

Where SymFactorID = @ID

Select @OldFactor = @NewFactor

Fetch Next From Factors2Process

Into @Part, @NewFactor, @ID

end

Close Factors2Process

Deallocate Factors2Process

--Commit Transaction

--Rollback Transaction

Create Table #link(FromID int, ToID int, Seq int)

Insert Into #link

Select Distinct

MIN(SymFactorID)

,MAX(SymFactorID)

,Seq

from

SymFactor2 sf_a

Group By

Seq

Select

frm.PartNumber as 'PartFrom'

,t.PartNumber as 'PartTo'

,frm.Factor as 'Factor'

From

#link

Inner Join SymFactor2 frm

on #link.FromID = frm.SymFactorID

Inner Join SymFactor2 t

on #link.ToID = t.SymFactorID

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

Try this:

select Factor,min(PartNumber) as PartFrom,max(PartNumber) as PartTo

from Factors2Process

group BY Factor,stuff(PartNumber,7,5,'')

order by Factor,PartFrom

And by the way - there is no order by on the query in your cursor definition...you should have one.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ZA_Crafty Ten Centuries Points: 1228 More actions · Answer 6

mister.magoo (2/24/2011)
Try this:
select Factor,min(PartNumber) as PartFrom,max(PartNumber) as PartTo
from Factors2Process
group BY Factor,stuff(PartNumber,7,5,'')
order by Factor,PartFrom
And by the way - there is no order by on the query in your cursor definition...you should have one.

From the Actual data, this query returns 13K Ranges.

The Cursor groups it into 500 ranges.

Yeah the table the cursor selects from is pre-formated. I will add the sorting, but do not think it will make a difference. I double checked the cursor by 1st printing the ID to make sure the order is correct. it printed 1, 2, 3, 4 etc... so I'm Happy. Then I also double checked the 1st few ranges, and they came out the same as if I would have done them manually. I'm fairly happy with the results, except I did not expect 500 ranges. I really though it would be about 100.

I would still LOVE to see if there is a non cursor way of doing this.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

ZA_Crafty (2/24/2011)
mister.magoo (2/24/2011)
Try this:
select Factor,min(PartNumber) as PartFrom,max(PartNumber) as PartTo
from Factors2Process
group BY Factor,stuff(PartNumber,7,5,'')
order by Factor,PartFrom
And by the way - there is no order by on the query in your cursor definition...you should have one.
From the Actual data, this query returns 13K Ranges.
The Cursor groups it into 500 ranges.
Yeah the table the cursor selects from is pre-formated. I will add the sorting, but do not think it will make a difference. I double checked the cursor by 1st printing the ID to make sure the order is correct. it printed 1, 2, 3, 4 etc... so I'm Happy. Then I also double checked the 1st few ranges, and they came out the same as if I would have done them manually. I'm fairly happy with the results, except I did not expect 500 ranges. I really though it would be about 100.
I would still LOVE to see if there is a non cursor way of doing this.

The number of ranges produced by this query is dependent on the way you want to group - in my case I used the first and third parts of the PartNumber columns as that seemed to match your expected output (and the results matched your expected output). Could you please clarify what defines a range in your data?

The cursor you are using is not guaranteed to always return the data in the expected order - that is what an ORDER BY clause does for you - it guarantees the order. Just because you have checked a few times and it seems right does not mean it will always be without an ORDER BY.

The query I posted will do what you want if you can just define what criteria should be used to define a range.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ZA_Crafty Ten Centuries Points: 1228 More actions · Answer 8

mister.magoo (2/25/2011)
ZA_Crafty (2/24/2011)
mister.magoo (2/24/2011)
Try this:
select Factor,min(PartNumber) as PartFrom,max(PartNumber) as PartTo
from Factors2Process
group BY Factor,stuff(PartNumber,7,5,'')
order by Factor,PartFrom
And by the way - there is no order by on the query in your cursor definition...you should have one.
From the Actual data, this query returns 13K Ranges.
The Cursor groups it into 500 ranges.
Yeah the table the cursor selects from is pre-formated. I will add the sorting, but do not think it will make a difference. I double checked the cursor by 1st printing the ID to make sure the order is correct. it printed 1, 2, 3, 4 etc... so I'm Happy. Then I also double checked the 1st few ranges, and they came out the same as if I would have done them manually. I'm fairly happy with the results, except I did not expect 500 ranges. I really though it would be about 100.
I would still LOVE to see if there is a non cursor way of doing this.
The number of ranges produced by this query is dependent on the way you want to group - in my case I used the first and third parts of the PartNumber columns as that seemed to match your expected output (and the results matched your expected output). Could you please clarify what defines a range in your data?
The cursor you are using is not guaranteed to always return the data in the expected order - that is what an ORDER BY clause does for you - it guarantees the order. Just because you have checked a few times and it seems right does not mean it will always be without an ORDER BY.
The query I posted will do what you want if you can just define what criteria should be used to define a range.

a range is defined by volume of sales. Also the range can overlap. thus a - c is factor 1, d is factor 2 then e-g is gain factor one. So you cannot simply say a to g is factor 1

If this makes sense...

So the way I have it, order 1st by part number, hen by factor, thn for each change in factor the first and las part is the range.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 9

Ok, so based on your latest bit of information, try this:

sorry - hadn't spotted that you are on sql 2000...removed code that won't work for you...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004548 More actions · Answer 10

ZA_Crafty (2/25/2011)
mister.magoo (2/25/2011)
ZA_Crafty (2/24/2011)
mister.magoo (2/24/2011)
Try this:
select Factor,min(PartNumber) as PartFrom,max(PartNumber) as PartTo
from Factors2Process
group BY Factor,stuff(PartNumber,7,5,'')
order by Factor,PartFrom
And by the way - there is no order by on the query in your cursor definition...you should have one.
From the Actual data, this query returns 13K Ranges.
The Cursor groups it into 500 ranges.
Yeah the table the cursor selects from is pre-formated. I will add the sorting, but do not think it will make a difference. I double checked the cursor by 1st printing the ID to make sure the order is correct. it printed 1, 2, 3, 4 etc... so I'm Happy. Then I also double checked the 1st few ranges, and they came out the same as if I would have done them manually. I'm fairly happy with the results, except I did not expect 500 ranges. I really though it would be about 100.
I would still LOVE to see if there is a non cursor way of doing this.
The number of ranges produced by this query is dependent on the way you want to group - in my case I used the first and third parts of the PartNumber columns as that seemed to match your expected output (and the results matched your expected output). Could you please clarify what defines a range in your data?
The cursor you are using is not guaranteed to always return the data in the expected order - that is what an ORDER BY clause does for you - it guarantees the order. Just because you have checked a few times and it seems right does not mean it will always be without an ORDER BY.
The query I posted will do what you want if you can just define what criteria should be used to define a range.
a range is defined by volume of sales. Also the range can overlap. thus a - c is factor 1, d is factor 2 then e-g is gain factor one. So you cannot simply say a to g is factor 1
If this makes sense...
So the way I have it, order 1st by part number, hen by factor, thn for each change in factor the first and las part is the range.

With all of that in mind, I've not checked your cursor. I do have some code that will resolve a million row table in just a couple of seconds, though. Rather than me talk about it, I'll let the comments in the code do the talking...

First, your original test data with some added rows just to proof the pudding...

/*****************************************************************

Build a test table. Note that this is NOT a part of the solution.

*****************************************************************/

--===== Do this experiment in a nice safe place that everyone has

USE TempDB

;

--===== Conditionally drop the test table to make reruns easier

IF OBJECT_ID('TempDB.dbo.XlsDump','U') IS NOT NULL

DROP TABLE dbo.XlsDump

;

--===== Create the test table

CREATE TABLE dbo.XlsDump

(

PartNumber VARCHAR(50),

Factor DECIMAL(18,10)

)

;

--===== Populate the test table. Notice the extra data.

INSERT INTO dbo.XlsDump

(PartNumber, Factor)

SELECT '35100-07g00-019', '0.2129380000' UNION ALL

SELECT '35100-07g01-019', '0.2129380000' UNION ALL

SELECT '35100-07g02-019', '0.2129380000' UNION ALL

SELECT '65310-07G01-000', '0.2129380000' UNION ALL

SELECT '65310-07G02-000', '0.2129380000' UNION ALL

SELECT '65310-07G03-000', '0.2129380000' UNION ALL

--------------------------------------------------------------------

SELECT '65310-07G04-000', '0.3000000000' UNION ALL

SELECT '65310-07G05-000', '0.3000000000' UNION ALL

SELECT '65310-07G06-000', '0.3000000000' UNION ALL

SELECT '65310-07G07-000', '0.2129380000' UNION ALL

SELECT '65310-07G08-000', '0.2129380000' UNION ALL

SELECT '65310-07G09-000', '0.2129380000' UNION ALL

--------------------------------------------------------------------

SELECT '11400-95855-000', '0.2293830000' UNION ALL

SELECT '11400-95856-000', '0.2293830000' UNION ALL

SELECT '11400-95857-000', '0.2293830000' UNION ALL

SELECT '61000-00001-000', '0.2293830000' UNION ALL

SELECT '61000-00002-000', '0.2293830000' UNION ALL

SELECT '61000-00003-000', '0.2293830000' UNION ALL

SELECT '24356-89000-000', '0.3344860000' UNION ALL

SELECT '24356-89001-000', '0.3344860000' UNION ALL

SELECT '24356-89002-000', '0.3344860000' UNION ALL

SELECT '51400-00001-000', '0.3344860000' UNION ALL

SELECT '51400-00002-000', '0.3344860000' UNION ALL

SELECT '51400-00003-000', '0.3344860000'

;

--===== Display the contents of the test table.

SELECT * FROM dbo.XlsDump

;

... and the solution. Be sure to read the article referenced in the code. If you don't follow all of the rules, it could give you the wrong answer and we can't have that. If you do follow the rules, then no worries. The only thing you really have to get used to is how quickly it blows the doors off any other method in SQL Server 2000.

/*****************************************************************

Now we solve the problem using a "checked" "Quirky Update".

Please see the article at the following URL for how it works.

http://www.sqlservercentral.com/articles/T-SQL/68467/

The "check" isn't currently in the article.

*****************************************************************/

--===== Conditionally drop the work table to make reruns easier

IF OBJECT_ID('TempDB..#MyHead','U') IS NOT NULL

DROP TABLE #MyHead

;

--===== Populate the work table from the original data making some translations on the way

SELECT RowNum = IDENTITY(INT,1,1),

PartNumber,

Part = CAST(SUBSTRING(PartNumber,1,CHARINDEX('-',PartNumber)-1) AS VARCHAR(10)),

Factor,

GroupNumber = CAST(NULL AS INT)

INTO #MyHead

FROM dbo.XlsDump

ORDER BY PartNumber, Factor

OPTION (MAXDOP 1) --absolutely essential here

;

--===== Create the quintessential clustered index in the order we need.

-- This should be the only index on this temp table if you can help it.

CREATE UNIQUE CLUSTERED INDEX IX_Composite

ON #MyHead (RowNum,Part,Factor)

WITH FILLFACTOR = 100

;

--===== Declare and preset some variables to support the "Quirky Update"

DECLARE @Counter INT,

@GroupNumber INT,

@PrevPart VARCHAR(10),

@PrevFactor DECIMAL(18,10)

;

SELECT @Counter = 1,

@GroupNumber = 0

;

--===== Do an update just like you would with a cursor but MUCH faster.

-- This just does some grouping based on part and factor.

UPDATE tgt

SET @GroupNumber = GroupNumber = CASE --@Counter is used as an error detector

WHEN @Counter = RowNum

THEN CASE

WHEN Part = @PrevPart AND Factor = @PrevFactor

THEN @GroupNumber

ELSE @GroupNumber + 1

END

ELSE 1/0 --Force error if counter gets out of sync with RowNum for ANY reason

END,

@PrevPart = Part,

@PrevFactor = Factor,

@Counter = @Counter + 1

FROM #MyHead tgt WITH (TABLOCKX) --absolutely required

OPTION (MAXDOP 1) --absolutely required

;

--===== Display the desired result.

SELECT Factor,

PartFrom = MIN(PartNumber),

PartTo = MAX(PartNumber)

FROM #MyHead

GROUP BY Factor, GroupNumber

ORDER BY PartFrom

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004548 More actions · Answer 11

Sorry... I almost forgot. Here are the results I get from the final SELECT in the solution...

FactorPartFromPartTo

0.229383000011400-95855-00011400-95857-000

0.334486000024356-89000-00024356-89002-000

0.212938000035100-07g00-01935100-07g02-019

0.334486000051400-00001-00051400-00003-000

0.229383000061000-00001-00061000-00003-000

0.212938000065310-07G01-00065310-07G03-000

0.300000000065310-07G04-00065310-07G06-000

0.212938000065310-07G07-00065310-07G09-000

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ZA_Crafty Ten Centuries Points: 1228 More actions · Answer 12

Jeff! You are my hero. Will go through it again, but I think i got most of it.

I simply love the force error part...

ELSE 1/0

and #MyHead... very creative

Jeff Moden SSC Guru Points: 1004548 More actions · Answer 13

Heh... thanks for the feedback, ZA. Most people don't get the pun built into that particular table name.

Just as a reminder and a friendly suggestion... the method I posted only works correctly if you follow all the rules. If you haven't taken the time to read the article at the link I posted, at least take the time to read the rules posted near the end of that article.

Last but not least, if there's anything you don't understand about the code, please ask! There's nothing worse than having to support code if you aren't 100% sure of how it works.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ZA_Crafty Ten Centuries Points: 1228 More actions · Answer 14

OK, so Now a curve ball was dealt.

Not all part numbers follow the same format.

As in my sample, most parts has the format:

00000-000-000

I just came across some partnumbers that crash on line

Part = CAST(SUBSTRING(PartNumber,1,CHARINDEX('-',PartNumber)-1) AS VARCHAR(10)),

They do not contain any "-" characters

This is just for reference? so taking the 1st 7chars for instance and stripping off any special chars like "-" should do the trick?

Grouping and crosstab like behaviour.... hairy query

Cookies on SQLServerCentral