Yet another version and result run on really slow laptop

--===== Conditionally drop the test table to make reruns in SSMS easier.

-- This is NOT a...

DECLARE @t TABLE(Sno int,Col varchar(20))

INSERT INTO @t(Sno,Col)

SELECT 1, '01112300' UNION ALL

SELECT 2, '00100' UNION ALL

SELECT 3, '01234567800' UNION ALL

SELECT 4, '00023456800' UNION ALL

SELECT 5, '012005670' UNION ALL

SELECT 6, '0030560';

SELECT Sno,

...

Cadavre (8/6/2012)

---

Celko forgot to account for the REPLACE...

Million row test for three of the solutions. Results are interesting/surpising. Perhaps some of you folks could check this and run it.

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

Another way, up to BIGINTs only

DECLARE @num BIGINT = 985;

WITH Tens(Pos,Val) AS (

SELECT 1, CAST(1 AS BIGINT) UNION ALL

SELECT 2, CAST(10 AS BIGINT) UNION ALL

SELECT 3, CAST(100 AS BIGINT) UNION...

JWOL (8/3/2012)

---

declare @xml xml

set @xml='<batch>

<statements>

<StmtSimple id="1"/>

<QueryPlan id="1"/>

<StmtCond id="2"/>

<QueryPlan id="1"/>

<StmtSimple id="8"/>

<StmtSimple id="16"/>

<StmtSimple id="32"/>

<StmtSimple id="64"/>

<StmtSimple id="128"/>

</statements>

...

Here's another, purely numeric, doesn't use SUBSTRINGs

DECLARE @num INT = 102948092;

WITH Tens(Pos,Val) AS (

SELECT 1, 1 UNION ALL

SELECT 2, 10 UNION ALL

SELECT 3, 100 UNION ALL

SELECT 4, 1000 UNION ALL

SELECT...

+1

Here's another way

select x.value('(@id)[1]','int'),

x.value('for $a in . return 1+count($a/../*[. << $a])','int')

from @xml.nodes('/r/bill/bob') c(x)

DECLARE @ParentChildMapping TABLE(ChildID INT, ParentID INT);

INSERT INTO @ParentChildMapping(ChildID,ParentID)

VALUES(1,0),(2,1),(3,1),(4,2),(5,2),(6,2),(7,4);

DECLARE @DataTable TABLE(ID INT, Name VARCHAR(20),somedata VARCHAR(20));

INSERT INTO @DataTable(ID,Name,somedata)

VALUES (1 ,'A' ,'xxx'),(2 ,'B' ,'yyy'),(5 ,'C' ,'zzz');

DECLARE @ID INT;

SET @ID = 5;

--SET @ID =...

sj 74463 (7/29/2012)

---

It is simply not a correct question and answer. the answer MIGHT be what the author of the question says, but it can also be 1,1. It depends...

Here's another way

DECLARE @t TABLE(time CHAR(5), status INT)

INSERT INTO @t(time,status)

VALUES('14:00',0),

('15:00', 0),

('16:00', 1),

('17:00', 2),

('18:00', 2),

('19:00', 1),

('20:00', 0),

('21:00', 0),

('22:00', 2),

('23:00', 1),

('24:00', 0);

WITH CTE1 AS (

SELECT time,status,

...

Have a look at the MERGE statement

In your code change

--recursive execution

SELECT

T.Childorgunitcode

,T.Orgunitcode

,RecursiveCTE.RecLevel + 1

to

--recursive execution

SELECT

T.Childorgunitcode

,RecursiveCTE.Parent

,RecursiveCTE.RecLevel + 1

Try using NULLIF

cogs/NULLIF(sales,0)