SQLTeam.com | Weblogs | Forums

Merge Data fields from different tables into one view table


I'm looking to combine data from different tables to result in a Case Number.

col.RequestID = 1234
col.RequestYear = 2021
col.RequestRegion = N

col.FirstName = Derrick
col.LastName = Johnson

The result of the view table should be:

col.RequestID = 1234
col.CaseNumberDisplay = "N-1234-Johnson-2021"

What is the best way to accomplish getting the CaseNumberDisplay? It is easier for the users to reference this field for reports.

Thank you

Please provide usable ddl and dml

Create table xyz
Insert into

Also how are these tables related which column? since we do not know if the columns are int, varchar etc, it would just be guess work

create view vw_CaseNumberDisplay
select concat(RequestRegion, '-', RequestID, '-', RequestYear)