Repeat SQL query and create one output column

Question

I am stuck with a weird problem that's killing me. I have a table, which basically boils down to this:

CREATE TABLE #Sample (ColA VARCHAR (20), ColB VARCHAR (20), ColC VARCHAR (20), ColD VARCHAR (10), ColE VARCHAR (10) )

INSERT INTO #Sample VALUES ('6250', '1001', '12AA', '12AA', ''),
                           ('6250', '1002', '12AA', '12AA', ''),
                           ('6251', '1003', '12BB', '12BB', 'A'),
                           ('6252', '1004', '12CC', '12CC', '')

I have received specifications for a shipping order form. For each value of ColB, I have to repeat the following steps, such that I create one output column. The first two rows are text, the second and third are prefixes + values from columns B and C. As a bonus, only if ColE is filled, we must select a prefix plus ColE.

This probably sounds confusing, so I will add the desired output:

'A1 Text'
'A2 Text'
'ZZ62501001' -- ZZ prefix + ColA + ColB
'ZZ12AA' -- ZZ prefix + ColC
'A1 Text'
'A2 Text'
'ZZ62501002' -- ZZ prefix + ColA + ColB
'ZZ12AA' -- ZZ prefix + ColC
'A1 Text'
'A2 Text'
'ZZ62511003' -- ZZ prefix + ColA + ColB
'ZZA' -- ZZ prefix + ColE (notice that this is only shown when ColE is filled)
'ZZ12BB' -- ZZ prefix + ColC
'A1 Text'
'A2 Text'
'ZZ62521004' -- ZZ prefix + ColA + ColB
'ZZ12CC' -- ZZ prefix + ColC

There is so much wrong with this set-up, but I have no influence over the matter. So I am stuck with the issue at hand.

My attempt was to create a 'regular' output table first and then try to create a new one with one column.

My code attempt, which is quite poor, but I will include it anyways:

CREATE TABLE #Output ( ColA VARCHAR (100),
                       ColB VARCHAR (100),
                       ColC VARCHAR (100),
                       ColD VARCHAR (100),
                       ColE VARCHAR (100) )


DECLARE @Counter INT 
SET @Counter = 1

WHILE @Counter < ( SELECT MAX (RowNumb) FROM #Sample )

BEGIN 

    INSERT INTO #Output
    SELECT 'A1 Text',
         'A2 Text',
         'ZZ' + ColA + ColB, -- ZZ prefix + ColA + ColB
         'ZZ' + ColC,
         'ZZ' + ColE
    FROM #Sample

SET @Counter = @Counter + 1

END

SELECT * FROM #Output

If anyone can solve this odd issue I have and create the desired output, I would really appreciate it.

Performance is not a real issue here, dealing with a couple of thousands of rows, so a cursor or loop is not an issue.

Answer 1

Using cross apply let's you build multiple rows per line:

declare @Sample table (ColA VARCHAR (20), ColB VARCHAR (20), ColC VARCHAR (20), ColD VARCHAR (10), ColE VARCHAR (10) )

INSERT INTO @Sample VALUES ('6250', '1001', '12AA', '12AA', ''),
                           ('6250', '1002', '12AA', '12AA', ''),
                           ('6251', '1003', '12BB', '12BB', 'A'),
                           ('6252', '1004', '12CC', '12CC', '')



select a.results
from @Sample
    cross apply (values ('A1 Text')
                        ,('A2 Text')
                        ,('ZZ' + ColA + ColB)
                        ,('ZZ' + ColC)
                        ,('ZZ' + ColE)
                ) a(results)
where a.results != 'ZZ'

Results:

results
A1 Text
A2 Text
ZZ62501001
ZZ12AA
A1 Text
A2 Text
ZZ62501002
ZZ12AA
A1 Text
A2 Text
ZZ62511003
ZZ12BB
A1 Text
A2 Text
ZZ62521004
ZZ12CC