How to assign multiple values for same dimension in SQL

Question

I have a table with business and code, for business and code combination, I have to assign the roles defined by business.

CREATE TABLE dbo.business
(
    Business varchar(50),
    Code     varchar(10),
    Role     varchar(50)
)

INSERT INTO dbo.business
    SELECT 'Sales', '9000', NULL
    UNION
    SELECT 'Sales', '9000', NULL
    UNION
    SELECT 'Mortgage', '5000', NULL
    UNION
    SELECT 'Mortgage', '5200', NULL
    UNION
    SELECT 'Sales', '9100', NULL

In above table, for combination of Sales and codes, certain roles are defined. I have to insert the roles.

For example, if business is Mortgage and code is 5000, then I have to assign three roles: R_Specialist, ProjectManager, RuralManager.

Expected output

Business    Code   Role
----------------------------------
Mortgage    5000   R_Specialist
Mortgage    5000   ProjectManager
Mortgage    5000   RuralManager
Mortgage    5200   C_Specialist
Mortgage    5200   ProjectManager
Mortgage    5200   RuralManager
Sales       9000   TechnicalManager
Sales       9100   Specialist

I can get the expected output, by doing UNION and select for each combination but I am ending up with multiple UNIONs. I have many business and codes in my actual data.

Is there a different way to get the expected output?

Answer 1

(this answer gives a glimpse on technics working on multiple RDBMS)

Simplified writing with VALUES

Some RDBMS allow the VALUES keyword to push multiple entries at once:

INSERT INTO business VALUES
    ('Sales', '9000', NULL),
    ('Sales', '9000', NULL),
    ('Mortgage', '5000', NULL),
    ('Mortgage', '5200', NULL),
    ('Sales', '9100', NULL);

Latest versions of PostgreSQL, SQLite, MySQL, Oracle, SQL Server all accept this simplified syntax.

(see an example in PostgreSQL)

Grouping adds

If you want to insert multiple roles per Business, or a Role on multiple Businesses,
depending on your RDBMS you can insert from a select from a temp table / a reference table / an array.

If you have a reference table of all possible roles

You can pick in it with an IN to create combinations:

INSERT INTO business
    SELECT 'Mortgage', 5200, role
    FROM all_roles WHERE role IN ('C_Specialist', 'ProjectManager', 'RuralManager');

If you want to add businesses and codes to a new role, as you have already inserted businesses / code pairs in your table you can select from it instead of from a new reference table:

INSERT INTO business
    SELECT DISTINCT Business, Code, 'ProjectManager'
    FROM business WHERE Code IN (5000, 5200);

(do not forget the DISTINCT because this is not a 1-row-per-business as a ref table would!)

With arrays UNNESTing

Some RDBMS will allow you to work with arrays, and generate one row per value:

INSERT INTO business
    SELECT 'Mortgage', 5200, role
    FROM UNNEST(ARRAY[ 'C_Specialist', 'ProjectManager', 'RuralManager' ]) AS all_roles(role);

Or the same with the keyword VALUES:

INSERT INTO business
    SELECT 'Mortgage', 5200, role
    FROM (VALUES ('C_Specialist'), ('ProjectManager'), ('RuralManager')) AS all_roles(role);

(note that VALUES works on rows, here of 1 column each, thus the ( ) around each role)

This one runs on SQL Server 2019.

Quick and dirty

As the optimization you're looking for is not processor-bounded but hand-typing bounded,
you may be looking for a simple string splitting solution.

Here in SQL Server:

INSERT INTO business
    SELECT 'Mortgage', 5200, * FROM STRING_SPLIT('C_Specialist,ProjectManager,RuralManager', ',');

Or you can even digest a whole multiline blob of all your roles:

WITH l0 AS
(
    SELECT
        LEFT(value, CHARINDEX(':', value) - 1) AS Business,
        RIGHT(value, LEN(value) - CHARINDEX(':', value)) AS value
    FROM
    STRING_SPLIT(TRIM(' '+CHAR(9)+CHAR(13)+CHAR(10) FROM '
Mortgage:5000:R_Specialist,ProjectManager,RuralManager
Mortgage:5200:C_Specialist,ProjectManager,RuralManager
Sales:9000:TechnicalManager
Sales:9100:Specialist
    '), CHAR(10))
),
l1 AS
(
    SELECT
        Business,
        LEFT(value, CHARINDEX(':', value) - 1) Code,
        RIGHT(value, LEN(value) - CHARINDEX(':', value)) AS Roles
    FROM l0
)
INSERT INTO Business
SELECT Business, Code, value AS Role
FROM l1 CROSS APPLY STRING_SPLIT(Roles, ',');

(with the corresponding fiddle)