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

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
LVL 1
stephenlecomptejrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you do the join like this:

select t1.WBS1      , t2.Label Discipline
  from table1 t1
  left join table2 t2
   on t1.Org like '%:' + t2.Code

Open in new window

stephenlecomptejrAuthor Commented:
Thank you very much for your reply, Guy.

That produces two columns and a discipline description for every WBS1 but if you were to take a look at the Excel attachment on the 3rd sheet - I really need this to just show the one WBS1 and then a comma after every discipline description (as I described above in my original question)

Is that something that is also possible?
stephenlecomptejrAuthor Commented:
The format and view I'm looking for as a final result is after the question:  How do I go about creating a view that as my example would show as the following?

I did make an error however.... for WBS1, 9414 should not be repeated twice.

It should look like this instead:

WBS1      Discipline
23      ACOUSTICS
5793      AUDIOVISUAL
9413      REIMBURSABLE
9414      ACOUSTICS, GENERAL
9415      ACOUSTICS, REIMBURSABLE
9416      GENERAL
9455      INFO TECHNOLOGY
9456      GENERAL
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Download your eBook!
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see ...
so you want what is know in mysql server as group_concat.
can be emulated like this, for example:
select t1.WBS1      
 , stuff((select ', '+ t2.Label as "data()"
	    from table2 t2
         where t1.Org like '%:' + t2.Code
		for xml path('')), 1, 2, '')  Discipline
  from table1 t1
group by t1.WBS1

Open in new window

stephenlecomptejrAuthor Commented:
Hi Guy,

I get an error when I try to run the above:

Msg 8120, Level 16, State 1, Line 4
Column 'table1.Org' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
stephenlecomptejrAuthor Commented:
Nevermind,

I got it working with

select t1.WBS1      
 , stuff((select ', '+ t2.Label as "data()"
          from table2 t2
         where t1.Org like '%:' + t2.Code
            for xml path('')), 1, 2, '')  Discipline
  from table1 t1
group by t1.WBS1, t1.Org


Thank you tremendously.   I'm very green at this.
stephenlecomptejrAuthor Commented:
The problem I'm having now is I'm not supposed to show any REIMBURSABLE or GENERAL disciplines.

I was just going to add a where clause but I don't even know where to begin in the SQL statement?

Please help one more time.
stephenlecomptejrAuthor Commented:
To me the problem where in that statement do I include to not show REIMBURSABLE, GENERAL or NULL.  In fact there is about 4 other disciplines we do not want to show besides the 3.

And where it gets tricky is that some disciplines will have like GENERAL, CORPORATE for one WBS1.   In that case all I would want to show is CORPORATE for that particular WBS1.
stephenlecomptejrAuthor Commented:
If you want me to post as another question - I can do that.  Please let me know what you would like to do!
Guy Hengel [angelIII / a3]Billing EngineerCommented:
just add the where clause as needed in the subquery: 
select t1.WBS1      
 , stuff((select ', '+ t2.Label as "data()"
          from table2 t2
         where t1.Org like '%:' + t2.Code
              and t2.code not in ( 'RE', '00' )
            for xml path('')), 1, 2, '')  Discipline
  from table1 t1
group by t1.WBS1, t1.Org

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stephenlecomptejrAuthor Commented:
Dude you are the best.  Thank you and much appreciation!
stephenlecomptejrAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.