We help IT Professionals succeed at work.
Get Started

Please help with creating a view between two tables and a column that needs to be parsed.

622 Views
Last Modified: 2021-04-21
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
Comment
Watch Question
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This problem has been solved!
Unlock 1 Answer and 12 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE