Better way to do this (set and call parameters)-CodePudding

I'm trying to have this result :

Those two lines are from the same query, but I must duplicate the result (first called "CLI" and second "CPA").

Currently I do it with an UNION :

WITH Temp AS (SELECT Code, Name, ...)

SELECT 'CLI' as RecordType, Code, Name, CONCAT('CLI', Name) as TechnicalName, ... FROM Temp
UNION ALL
SELECT 'CPA' as RecordType, Code, Name, CONCAT('CPA', Name) as TechnicalName, ... FROM Temp

Is there any other solution most efficient ? Because there are about 30 columns, so the code is very big now.

I thought of something like this :

DECLARE @P_RecordType as char(3);

WITH Temp AS (SELECT Code, Name, ...)

SET @P_RecordType = 'CLI'
SELECT * FROM Temp
UNION ALL
SET @P_RecordType = 'CPA'
SELECT * FROM Temp

But it doesn't work.

PS: I'm querying on SSMS from "Microsoft SQL Azure (RTM) - 12.0.2000.8".

CodePudding user response：

You can try to use CROSS APPLY with value to make it simple.

Query 1:

SELECT v.c as RecordType, Code, Name, CONCAT(v.c, Name) as TechnicalName
FROM Temp
CROSS APPLY(VALUES ('CPA'),('CLI')) v (c)

Results: