asked on

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

Guy Hengel [angelIII / a3]

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

Stephen LeCompte

ASKER

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?

Stephen LeCompte

ASKER

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

Guy Hengel [angelIII / a3]

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

Stephen LeCompte

ASKER

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.

Stephen LeCompte

ASKER

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.

Stephen LeCompte

ASKER

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.

Stephen LeCompte

ASKER

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.

Stephen LeCompte

ASKER

If you want me to post as another question - I can do that. Please let me know what you would like to do!

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Stephen LeCompte

ASKER

Dude you are the best. Thank you and much appreciation!

Stephen LeCompte

ASKER

Hi Guy,

I had one more question regarding this...

https://www.experts-exchange.com/questions/28334379/T-SQL-Remove-NULL-values-from-query.html