All, please help me with this since I am new to SQL Server but I'm trying to create a view where one column from a table needs to be parsed out and then related to another table.
Please note data from first table is shown as follows: (I provided an Excel document as well in attachments.)
WBS1 WBS2 Name Org
23 ACX Hyatt Regency US:07:AC
23 REIMB NULL US:07:RE
23 ZZZ Default WBS2 US:07:AC
5793 AV10 Sch - Press Rm US:06:AV
5793 AV11 DD - Press Rm US:06:AV
5793 AV12 CD - Press Rm US:06:AV
5793 AV13 CA - Press Rm US:06:AV
5793 AV20 Sch - Public Sp US:06:AV
5793 AV21 DD - Public Sp US:06:AV
9413 REIMB Reimbursable UU:06:RE
9414 CONGREGATION EMANU-EL UU:01:00
9414 AC5 MULTI PURPOSE ROOM UU:01:AC
9414 REIMB REIMB UU:01:00
9415 Popeyes W
9415 ACX Acoustical Consulting Service UU:11:AC
9415 REIMB Reimbursable UU:11:RE
9416 Ambulance UU:06:00
9455 TC1 Pre-Design Services UU:03:IT
9455 TC2 Phase 2A Center Building UU:03:IT
9455 TC3 Phase 2B Remaining Buildings UU:03:IT
9455 TC4 Phase 2C Amenity Spaces UU:03:IT
9455 TC5 Phase 3 UU:03:IT
9456 Jordan River UU:15:00
Tuesday Mornings 0
Data in 2nd table is:
Code Label
DC DATA CENTER
99 PERSONAL
RE REIMBURSABLE
IT INFO TECHNOLOGY
TE THEATER
ST STAFF
SC SECURITY
PM PROJECT MANAGEMENT
MK MARKETING
CA CADD
AV AUDIOVISUAL
AT ACCOUNTING
AC ACOUSTICS
00 GENERAL
¿ME MEDICAL EQUIPMENT
So basically the Org column that has colons separating contains the code in the 2nd table at the end. As an example the US:06:AV actually points to AV for AUDIOVISUAL in the 2nd table.
Thus I'm trying to create a view that gives WBS1, WBS2 and then a description of that code tied together in a relationship. This would be a lot easier if there was a column that only had the AV code but in this case I'm having to parse it out and then use it as such in a relationship. Then I have to take it a step further and if there are multiple disciplines show in a comma separated way.
How do I go about creating a view that as my example would show as the following?
WBS1 Discipline
23 ACOUSTICS
5793 AUDIOVISUAL
9413 REIMBURSABLE
9414 GENERAL
9414 ACOUSTICS, GENERAL
9415 ACOUSTICS, REIMBURSABLE
9416 GENERAL
9455 INFO TECHNOLOGY
9456 GENERAL
Again, please note attached Excel file with its 3 sheets as to what I'm trying to do.
Thank you in advance with any help!
SampleTables.xlsx