By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Data (
Name VARCHAR(100) NOT NULL,
[Product Code] VARCHAR(10) NOT NULL,
[Payment Type] VARCHAR(10) NOT NULL,
Member VARCHAR(10) NOT NULL
)
INSERT Data(Name, [Product Code], [Payment Type], Member)
VALUES
('Bob', 'Code A', 'Type 2', 'Yes'),
('Kat', 'Code B', 'Type 2', 'Yes'),
('Greg', 'Code C', 'Type 1', 'No'),
('David', 'Code A', 'Type 1', 'Yes'),
('Simon', 'Code A', 'Type 2', 'Yes')
5 rows affected
-- Generate more test data
INSERT Data(Name, [Product Code], [Payment Type], Member)
SELECT
CONCAT(A.Name, '_', S.Value) AS Name, -- Unique name
A.[Product Code],
A.[Payment Type],
A.Member
FROM GENERATE_SERIES(1, 1000) S
CROSS APPLY(
-- Generate mix of 12 Product-Code/Payment-Type/Member combinations and 5 names
SELECT
CHOOSE(S.value % 5 + 1, 'Bob', 'Kat', 'Greg', 'David', 'Simon') AS Name,
CHOOSE(S.value / 5 % 3 + 1, 'Code A', 'Code B', 'Code C') AS [Product Code],
CHOOSE(S.value / 15 % 2 + 1, 'Type 1', 'Type 2') AS [Payment Type],
CHOOSE(S.value / 30 % 2 + 1, 'Yes', 'No') AS Member
) A
1000 rows affected
SELECT TOP 25 *
FROM (
SELECT
*,
ROW_NUMBER() OVER(
PARTITION BY [Product Code], [Payment Type], Member
ORDER BY (SELECT NULL) -- Arbitrary
) AS RowNum
FROM Data
) D
ORDER BY D.RowNum
| Name | Product Code | Payment Type | Member | RowNum |
|---|---|---|---|---|
| Bob_30 | Code A | Type 1 | No | 1 |
| David | Code A | Type 1 | Yes | 1 |
| Bob_45 | Code A | Type 2 | No | 1 |
| Bob_915 | Code A | Type 2 | Yes | 1 |
| Bob_995 | Code B | Type 1 | No | 1 |
| Bob_905 | Code B | Type 1 | Yes | 1 |
| Bob_50 | Code B | Type 2 | No | 1 |
| Bob_920 | Code B | Type 2 | Yes | 1 |
| Greg | Code C | Type 1 | No | 1 |
| Bob_910 | Code C | Type 1 | Yes | 1 |
| Bob_55 | Code C | Type 2 | No | 1 |
| Bob_925 | Code C | Type 2 | Yes | 1 |
| Kat_31 | Code A | Type 1 | No | 2 |
| Bob_960 | Code A | Type 1 | Yes | 2 |
| Kat_46 | Code A | Type 2 | No | 2 |
| Kat_916 | Code A | Type 2 | Yes | 2 |
| Kat_996 | Code B | Type 1 | No | 2 |
| Kat_906 | Code B | Type 1 | Yes | 2 |
| Kat_51 | Code B | Type 2 | No | 2 |
| Kat_921 | Code B | Type 2 | Yes | 2 |
| Bob_1000 | Code C | Type 1 | No | 2 |
| Kat_911 | Code C | Type 1 | Yes | 2 |
| Kat_56 | Code C | Type 2 | No | 2 |
| Kat_926 | Code C | Type 2 | Yes | 2 |
| Greg_32 | Code A | Type 1 | No | 3 |
-- Same results, but ordered differently
SELECT *
FROM (
SELECT TOP 25 *
FROM (
SELECT
*,
ROW_NUMBER() OVER(
PARTITION BY [Product Code], [Payment Type], Member
ORDER BY (SELECT NULL) -- Arbitrary
) AS RowNum
FROM Data
) D
ORDER BY D.RowNum
) S
ORDER BY [Product Code], [Payment Type], Member, RowNum
| Name | Product Code | Payment Type | Member | RowNum |
|---|---|---|---|---|
| Bob_30 | Code A | Type 1 | No | 1 |
| Kat_31 | Code A | Type 1 | No | 2 |
| Greg_32 | Code A | Type 1 | No | 3 |
| David | Code A | Type 1 | Yes | 1 |
| Bob_960 | Code A | Type 1 | Yes | 2 |
| Bob_45 | Code A | Type 2 | No | 1 |
| Kat_46 | Code A | Type 2 | No | 2 |
| Bob_915 | Code A | Type 2 | Yes | 1 |
| Kat_916 | Code A | Type 2 | Yes | 2 |
| Bob_995 | Code B | Type 1 | No | 1 |
| Kat_996 | Code B | Type 1 | No | 2 |
| Bob_905 | Code B | Type 1 | Yes | 1 |
| Kat_906 | Code B | Type 1 | Yes | 2 |
| Bob_50 | Code B | Type 2 | No | 1 |
| Kat_51 | Code B | Type 2 | No | 2 |
| Bob_920 | Code B | Type 2 | Yes | 1 |
| Kat_921 | Code B | Type 2 | Yes | 2 |
| Greg | Code C | Type 1 | No | 1 |
| Bob_1000 | Code C | Type 1 | No | 2 |
| Bob_910 | Code C | Type 1 | Yes | 1 |
| Kat_911 | Code C | Type 1 | Yes | 2 |
| Bob_55 | Code C | Type 2 | No | 1 |
| Kat_56 | Code C | Type 2 | No | 2 |
| Bob_925 | Code C | Type 2 | Yes | 1 |
| Kat_926 | Code C | Type 2 | Yes | 2 |