sql adding additional rows to each row

Question

Is it possible to add additional rows to a selected set based on a field value?

I have this query:

WITH CTEDivisions AS
(
    SELECT ....
)

SELECT 
    cnt,
    Division
FROM CTEDivisions

Which returns:

  cnt         Division
  1           Sales
  2           Marketing
  1           Business
  2           IT
  etc...

What I need is a statement that returns 3 additional rows when cnt = 2, like:

 Division     NewDivision
 Sales        Sales
 Marketing    Marketing - X
 Marketing    Marketing - Y
 Marketing    Marketing - Z
 Business     Business
 IT           IT - X
 IT           IT - Y
 IT           IT - Z
 etc...

I've searched for ways of doing this, and found some possible solutions using a cursor and WHILE loop, but those don't seem to work in conjunction with the CTE statement.

Answer 1

Make a table for your extra rows (either as a CTE, or as a permanent table) and left join to it using your condition (cnt=2):

DECLARE @T TABLE(cnt int, Division varchar(100));
INSERT INTO @T(cnt, Division) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(1, 'Business'),
(2, 'IT');

WITH
CTEDivisions
AS
(
    SELECT
        cnt
        ,Division
    FROM @T
)
,CTE_Extra
AS
(
    SELECT ' - X' AS Extra
    UNION ALL
    SELECT ' - Y' AS Extra
    UNION ALL
    SELECT ' - Z' AS Extra
)
SELECT
    cnt
    ,Division
    ,Division + ISNULL(Extra, '') AS NewDivision
FROM
    CTEDivisions
    LEFT JOIN CTE_Extra ON CTEDivisions.cnt = 2
;

result set

cnt    Division    NewDivision
1      Sales       Sales
2      Marketing   Marketing - X
2      Marketing   Marketing - Y
2      Marketing   Marketing - Z
1      Business    Business
2      IT          IT - X
2      IT          IT - Y
2      IT          IT - Z

Answer 2

WITH CTEDivisions AS
(
    SELECT ....
)

SELECT 
    c.cnt,
    c.Division,
    c.Division + ISNULL(' - ' + o.v, '') AS NewDivision
FROM CTEDivisions c
OUTER APPLY(SELECT v FROM(VALUES('X'),('Y'),('Z'))t(v) WHERE c.cnt = 2)o