CODEWITHSUNDEEP
sqlsql-server
anarz
anarz

Reputation: 209

Populate list of values for each category

I extract the following table:

SELECT NAME FROM TABLE

  NAME
  A-1
  A-1
  A-1
  A-2
  A-2
  A-2
  A-2

and I have the list of values

['value_1', 'value_2', 'value_3', 'value_4', 'value_5',]

that should be populated for each unique name:

  NAME   VALUES
  A-1  value_1
  A-1  value_2
  A-1  value_3
  A-1  value_4
  A-1  value_5
  A-2  value_1
  A-2  value_2
  A-2  value_3
  A-2  value_4
  A-2  value_5

How it can be done in SQL? VALUES are entered as constants, not from any other table

Upvotes: 0

Views: 36

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522211

If the list of values actually be contained in another table, then a cross join would work here:

SELECT
    n.name,
    v.value
FROM names n
CROSS JOIN vals v
ORDER BY
    n.name,
    v.value;

Data:

WITH names AS (
    SELECT 'A-1' AS name UNION ALL
    SELECT 'A-2'
),
vals AS (
    SELECT 'value_1' AS value UNION ALL
    SELECT 'value_2' UNION ALL
    SELECT 'value_3' UNION ALL
    SELECT 'value_4' UNION ALL
    SELECT 'value_5'
)

Demo

Upvotes: 2

Related Questions