try this...

First some set up - your data - next time please do this for us...

IF OBJECT_ID('tempdb..#cnums') IS NULL

BEGIN

CREATE TABLE #cnums(Cnum bigint,D1 int, D2 int,D3 int, D4 int)

INSERT #cnums

SELECT 121345,11,12,NULL,NULL...

That does seem very slow for that number of rows - if you post the execution plan (de-sensitise it if you need to), maybe we can spot the problem?

Thanks for the feedback,

That's not too bad, but that still sounds slow - how much data is in your groups and items tables?

Are they indexed on gleaderid and id respectively?

Can...

Michael T2 (10/3/2012)

---

Thanks for doing that. I am going to try building a function to see if it is faster that way. Right now this query takes about 20 minutes...

Dwain,

I did have a read of some of the Swedish districts thread, but not in that much detail.

I guess the general approach with these multi-level recursion problems is to reduce...

Thanks for the validation dwain, and having read your thread, I thought that Jeff's preference for the WHILE loop must have some mileage, so I tried to speed things up...

There are two solutions to this amount:

2172,2173,2174, 2176,2177,2178,2179,2180,2181,2182,2183,2184,2185,2186,2187, 2189,2190,2191

2172,2173,2174,2175,2176,2177,2178,2179,2180,2181,2182,2183,2184,2185,2186,2187,2188,2189,2190

I have formatted them like that to highlight the differences.

The query is damn ugly...

I have turned your sample data into something that people can use - take note that you should do this , not us...

Things to note:

1. I used two CTE expressions...

It's really hard to give a decent answer without any sample data.

How about you provide some sample data and expected results and you will definitely get a decent answer 🙂

Yes, I agree - I might look into this, I am writing an addin at the moment and that would be great.

I don't know if it is possible, but I...

This is one way to go...

select min(class) as class,x.nc1, x.nc2

from #nc

cross apply (select case when nc1<nc2 then nc1 else nc2 end,case when nc1<nc2 then nc2 else nc1 end) x(nc1,nc2)

group...

<disclaimer> I don't even want to think about the security implications of what you are doing, so I will leave that to you</disclaimer>

Have you thought about using sqlcmd instead of...

+1 😀

Glad to hear you resolved that bit 🙂

here you go...any questions, just ask.

DECLARE @xml XML = '

<row id="PDPD1134200001" xml:space="preserve">

<c2>100113</c2>

<c3>KHR</c3>

<c4>1</c4>

<c5>TR</c5>

<c6>00</c6>

<c7>1</c7>

<c8>21050</c8>

<c9>B</c9>

<c10>10049</c10>

<c11>72.00</c11>

<c13>0</c13>

<c14>21050</c14>

<c15>9700.01</c15>

<c16>9700.01</c16>

<c17>-50000</c17>

<c18>50000</c18>

<c19>50000</c19>

<c20>0</c20>

<c21>IN</c21>

<c21 m="2">PR</c21>

<c21 m="3">PE</c21>

<c21 m="4">PS</c21>

<c22>0</c22>

<c22 m="2">50000</c22>

<c22 m="3">0</c22>

<c22 m="4">0</c22>

<c24>20111208</c24>

<c25>50000</c25>

<c26>0</c26>

<c27>100000</c27>

<c28>IN</c28>

<c28 m="1" s="2">PR</c28>

<c28 m="1" s="3">PE</c28>

<c28 m="1" s="4">PS</c28>

<c29>10000</c29>

<c29 m="1" s="2">90000</c29>

<c29 m="1" s="3">0</c29>

<c29 m="1" s="4">0</c29>

<c30>0</c30>

<c30...