SQL Union Create Custom Field to Different 2 Tables Output

Transact-SQL
1

Hi Experts, please advise

I made a UNION of 2 Tables named REP and SPR. It worked fine but I'm not able to differentiate which data is from REP or SPR.

How do I create a field/column named TYPE that will show REP OR SPR based on the output of the UNION

Thanks much

2

Can you post your query that you are using.

3

----REP DATA
SELECT

Rep.PR AS PR,
Rep.PO AS PO,
Rep.SO AS SO,
REP. YA AS YA

FROM

GAD_RAS AS Rep

UNION

-----SPR DATA
SELECT
Ord.SAP_PR AS PR,
Ord. SAP_PO AS PO,
Ord.SAP_SO AS SO,
Ord.SAP_YA AS YA

FROM
SAP_TRS AS Ord

Once the data outputs, how can I tell which PO,PR,SO,YA field is SPR OR REP?

Can I create a new custom column named TYPE that will tell me which is SPR OR REP.

Thanks

4 

SELECT
'REP' AS Tbl,
Rep.PR AS PR,
Rep.PO AS PO,
Rep.SO AS SO,
REP. YA AS YA
FROM
GAD_RAS AS Rep

UNION

-----SPR DATA
SELECT
'SPR' AS Tbl,
Ord.SAP_PR AS PR,
Ord. SAP_PO AS PO,
Ord.SAP_SO AS SO,
Ord.SAP_YA AS YA
FROM
SAP_TRS AS Ord
/*ORDER BY ...*/
5

ORDER BY sorts. This is not what I'm looking for.

I am trying to differentiate between days from the 2 tables. I want an output with new column named ORDER TYPE like below

PO PR SO. Order Type

  1.  A.    MA.          ord

  2.  B.     DA.          rep

  3.  C.     TA.          ord

  4.  D.      RA.         rep
6

You should also change the UNION to UNION ALL here. That may require adding DISTINCT to each query - if the individual queries are returning duplicates.

UNION will add a sort to remove duplicates - but each set would not have duplicates because of the 'type' column.

1 Like
7

I did add a column with the "Tbl" name. I just also added an optional ORDER BY clause.

1 Like