This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.
Course Of The Month
8 days, 20 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Please help with creating a view between two tables and a column that needs to be parsed.
Posted on 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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
9
3
12 Comments
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
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?
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?
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
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
I see ...
so you want what is know in mysql server as group_concat.
can be emulated like this, for example:
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
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.
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.
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.
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.
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.
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.
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.
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.
If you want me to post as another question - I can do that. Please let me know what you would like to do!
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
Hi Guy,
I had one more question regarding this...
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28334379.html
I had one more question regarding this...
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28334379.html
Featured Post
![[Webinar] Learn How Hackers Steal Your Credentials](https://filedb.experts-exchange.com/files/public/2017/5/21/e0b7778f-e79e-4b5d-9ec6-5496e40be42e.png)
Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
- Query Syntax
- SQL
- Databases
- MySQL Server
- MongoDB
- NoSQL Databases, Percona
In part one, we reviewed the prerequisites required for installing SQL Server vNext.
In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 20 hours left to enroll
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.