I don't think you'll need a tally/numbers table for this. A single query should do it.

INSERT INTO @ResultsTable

SELECT fis.SalesOrderNumber

, dp.EnglishProductName

...

Looks like a straightforward recursive query

DECLARE @t TABLE(Col1 CHAR(2), Col2 CHAR(2))

INSERT INTO @t(Col1,Col2)

VALUES

('G1', 'G2'),

('G3', 'P1'),

('P1', 'P2'),

('P2', 'G4'),

('G5', 'G6'),

('G7', 'G8');

WITH Recur AS (

SELECT t1.Col1 As Root,t1.Col2

FROM @t t1

WHERE NOT EXISTS(SELECT *...

Koen Verbeeck (8/8/2013)

---

Probably because Oracle is a sucky DB engine who evaluates the expression row by row, while SQL Server evaluates them all at the same time, resulting in both...

Nice question, definitely learned something new.

You don't actually need the '/', that's just for clarity to show the sort order for each part of the hierarchy. The sort order value just has to be fixed...

You're welcome.

Is this what you're after?

WITH Source AS (

SELECT [TIME_ID], , [DESCRIPTION], [PARENT_ID], CAST(1000+ROW_NUMBER() OVER(ORDER BY [SORTORDER]) AS VARCHAR(10)) AS [SORTORDER]

FROM tblTime),

Recur AS (

SELECT [TIME_ID], , [DESCRIPTION], [PARENT_ID], CAST([SORTORDER] AS VARCHAR(1000))...

Lots of ways of doing this, here's one

WITH dpCTE AS (

SELECT [nr],

[type],

[DT],

RANK() OVER(PARTITION BY [DT] ORDER BY...

Try this

WITH Starts AS (

SELECT a.StationID,a.dtmStart,

ROW_NUMBER() OVER(PARTITION BY a.StationID ORDER BY a.dtmStart) AS rn

FROM @Events a

WHERE NOT EXISTS(SELECT * FROM @Events b

...

Here's a query using '&'

http://www.sqlservercentral.com/Forums/FindPost1422007.aspx

You can use '&' for decoding @@OPTIONS

PRINT @@OPTIONS

IF ( (8 & @@OPTIONS) = 8 ) PRINT 'ANSI_WARNINGS'

IF ( (16 & @@OPTIONS) = 16 ) PRINT 'ANSI_PADDING'

IF ( (32 & @@OPTIONS)...

WITH CTE AS (

SELECT acctno , email_id,

ROW_NUMBER() OVER(PARTITION BY acctno ORDER BY CASE WHEN email_id LIKE '%dell.com%' THEN 0 ELSE 1 END, email_id)...

What are your current settings and the stored procedure settings for ANSI_NULLS, CONCAT_NULL_YIELDS_NULL etc?

DECLARE @T1 TABLE(ID INT, Dept CHAR(3), Cat INT)

INSERT INTO @T1(ID,Dept,Cat)

VALUES

(1, 'WER', 2),

(2, 'TTR', 7);

DECLARE @T2 TABLE(ID INT, [Date] DATE, Type CHAR(2))

INSERT INTO @T2(ID, [Date], Type)

VALUES

(1, '2013-07-01', 'GA'),

(2, '2013-07-04', 'FS'),

(2, '2013-07-08',...

Paul White posted an interesting item about MERGE here