SQL, selecting with a multi value field.

DECLARE @maxValueToGenerate INT
 
-- chg next value to the highest value you need; 
-- the current code supports up to 100M - 1, but you could increase that if you wanted to
 
SET @maxValueToGenerate = 16000
 
IF OBJECT_ID('seqNums') IS NOT NULL
    DROP TABLE seqNums
 
CREATE TABLE seqNums (
    seqNum INT,
    CONSTRAINT seqNums_CI --don't remove, improves performance!
        UNIQUE CLUSTERED (seqNum) WITH FILLFACTOR = 100
    )
 
INSERT INTO seqNums
SELECT [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] + 
	[1Ms] + [10Ms] /*+ [100Ms]*/
FROM (
    SELECT 0 AS [1s] UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL 
    SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL 
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS digits
CROSS JOIN (
    SELECT 00 AS [10s] UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL 
    SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL 
    SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90
) AS [10s]
CROSS JOIN (
	SELECT [100s]
	FROM (
    SELECT 000 AS [100s] UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL 
    SELECT 300 UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL 
    SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900
	) inlineData
	WHERE [100s] <= @maxValueToGenerate
) AS [100s]
CROSS JOIN (
	SELECT [1000s]
	FROM (
    SELECT 0000 AS [1000s] UNION ALL SELECT 1000 UNION ALL SELECT 2000 UNION ALL 
    SELECT 3000 UNION ALL SELECT 4000 UNION ALL SELECT 5000 UNION ALL SELECT 6000 UNION ALL 
    SELECT 7000 UNION ALL SELECT 8000 UNION ALL SELECT 9000
	) inlineData
	WHERE [1000s] <= @maxValueToGenerate
) AS [1000s]
CROSS JOIN (
	SELECT [10Ks]
	FROM (
    SELECT 00000 AS [10Ks] UNION ALL SELECT 10000 UNION ALL SELECT 20000 UNION ALL 
    SELECT 30000 UNION ALL SELECT 40000 UNION ALL SELECT 50000 UNION ALL SELECT 60000 UNION ALL 
    SELECT 70000 UNION ALL SELECT 80000 UNION ALL SELECT 90000
	) inlineData
	WHERE [10Ks] <= @maxValueToGenerate
) AS [10Ks]
CROSS JOIN (
	SELECT [100Ks]
	FROM (
    SELECT 000000 AS [100Ks] UNION ALL SELECT 100000 UNION ALL SELECT 200000 UNION ALL 
    SELECT 300000 UNION ALL SELECT 400000 UNION ALL SELECT 500000 UNION ALL SELECT 600000 UNION ALL 
    SELECT 700000 UNION ALL SELECT 800000 UNION ALL SELECT 900000
	) inlineData
	WHERE [100Ks] <= @maxValueToGenerate
) AS [100Ks]
CROSS JOIN (
	SELECT [1Ms]
	FROM (
    SELECT 0000000 AS [1Ms] UNION ALL SELECT 1000000 UNION ALL SELECT 2000000 UNION ALL 
    SELECT 3000000 UNION ALL SELECT 4000000 UNION ALL SELECT 5000000 UNION ALL SELECT 6000000 UNION ALL 
    SELECT 7000000 UNION ALL SELECT 8000000 UNION ALL SELECT 9000000
	) inlineData
	WHERE [1Ms] <= @maxValueToGenerate
) AS [1Ms]
CROSS JOIN (
	SELECT [10Ms]
	FROM (
    SELECT 00000000 AS [10Ms] UNION ALL SELECT 10000000 UNION ALL SELECT 20000000 UNION ALL 
    SELECT 30000000 UNION ALL SELECT 40000000 UNION ALL SELECT 50000000 UNION ALL SELECT 60000000 UNION ALL 
    SELECT 70000000 UNION ALL SELECT 80000000 UNION ALL SELECT 90000000
	) inlineData
	WHERE [10Ms] <= @maxValueToGenerate
) AS [10Ms]
/*
CROSS JOIN (
	SELECT [100Ms]
	FROM (
    SELECT 000000000 AS [100Ms] UNION ALL SELECT 100000000 UNION ALL SELECT 200000000 UNION ALL 
    SELECT 300000000 UNION ALL SELECT 400000000 UNION ALL SELECT 500000000 UNION ALL SELECT 600000000 UNION ALL 
    SELECT 700000000 UNION ALL SELECT 800000000 UNION ALL SELECT 900000000
	) inlineData
	WHERE [100Ms] <= @maxValueToGenerate
) AS [100Ms]
*/
WHERE [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] + [1Ms] + [10Ms] /*+ [100Ms]*/ <= @maxValueToGenerate
ORDER BY [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] + [1Ms] + [10Ms] /*+ [100Ms]*/
 
DBCC SHOWCONTIG(seqNums) --make sure table is highly contig for max performance
 
SELECT COUNT(*) FROM seqNums WITH (NOLOCK) --verify that the expected number of rows were generated
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:

drop table q24113595
GO
create table q24113595 (field1 varchar(10), field2 varchar(max))
GO
insert into q24113595
select 'Matt', 'QQ|XX|YY' union all
select 'Bob', 'AA|XX'
GO
 
WITH CTE (Field1, Field2, Remain, Level)
AS
(
-- Anchor member definition
	SELECT Field1, Field2 = Field2, Remain = Field2, Level = 0
	FROM q24113595
	UNION ALL
-- Recursive member definition
	SELECT Field1,
		Field2 = left(Remain, isnull(position_of_space, remain_length) -1),
		Remain = right(Remain, isnull(remain_length - position_of_space, 0)),
		Level = 1
	FROM
	(
	SELECT
		position_of_space = nullif(PatIndex('%|%', Remain), 0),
		remain_length = len(Remain),
		CTE.Field1, CTE.Remain
	FROM CTE
	INNER JOIN q24113595 on q24113595.Field1 = CTE.Field1
	WHERE Remain <> ''
	) X
)
-- Statement that executes the CTE
SELECT Field1, Field2
FROM CTE
WHERE Level = 1
GO
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:

CREATE VIEW v24113595
AS
WITH CTE (Field1, Field2, Remain, Level)
AS
(
-- Anchor member definition
        SELECT Field1, Field2 = Field2, Remain = Field2, Level = 0
        FROM q24113595
        UNION ALL
-- Recursive member definition
        SELECT Field1,
                Field2 = left(Remain, isnull(position_of_space, remain_length) -1),
                Remain = right(Remain, isnull(remain_length - position_of_space, 0)),
                Level = 1
        FROM
        (
        SELECT
                position_of_space = nullif(PatIndex('%|%', Remain), 0),
                remain_length = len(Remain),
                CTE.Field1, CTE.Remain
        FROM CTE
        INNER JOIN q24113595 on q24113595.Field1 = CTE.Field1
        WHERE Remain <> ''
        ) X
)
-- Statement that executes the CTE
SELECT Field1, Field2
FROM CTE
WHERE Level = 1
GO
 
--
 
SELECT * FROM v24113595
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34: