troubleshooting Question

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

Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
12 Comments1 Solution624 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros