Solved

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

Posted on 2014-01-09
12
374 Views
Last Modified: 2014-01-09
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
0
Comment
Question by:stephenlecomptejr
  • 9
  • 3
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39767774
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

0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39768013
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?
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39768029
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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39768041
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

0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39768062
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.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39768065
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39768071
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.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39768082
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.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39768085
If you want me to post as another question - I can do that.  Please let me know what you would like to do!
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39768091
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

0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39768103
Dude you are the best.  Thank you and much appreciation!
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39768163
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now