December 16, 2009 at 2:59 pm
Hi there,
I'm pretty new to this forum and this level of SQL queries so please be gentle:-)
I have to write a SQL query that extracts all the information regarding an applicant from all the related tables in the entire database to a single flat file (Excel propably).
I have successfully written most of it but have hit a snag when it comes to getting info from a certain table (containing multiple columns) where each applicant has multiple rows.
the tables concerned are:
tblApplication (Each applicant has a unique ID, applicantID, refering to a single row)
tblExperience (Each applicant has multiple rows but each row has a unique experience ID, ExperienceID, this table relates to previous job experience with employer details. This table also contains info relating to another table detailing job types but that should be sorted by the same type of query that will get this problem solved...)
SELECT
(
SELECT TOP 1 tblExperience.EmployerName
FROM (
SELECT TOP 2 tblExperience.ApplicantExperienceID, tblExperience.EmployerName
FROM (
SELECT TOP 3 tblExperience.ApplicantExperienceID, tblExperience.EmployerName
FROM (
SELECT TOP 4 tblExperience.ApplicantExperienceID, tblExperience.EmployerName
FROM (
SELECT TOP 5 tblExperience.ApplicantExperienceID, tblExperience.EmployerName
FROM tblExperience
WHERE tblExperience.ApplicationID = 1
ORDER BY tblExperience.ApplicantExperienceID
)AS TempEmployer
ORDER BY tblExperience.ApplicantExperienceID
)AS TempEmployer
ORDER BY tblExperience.ApplicantExperienceID
)AS TempEmployer
ORDER BY tblExperience.ApplicantExperienceID
)AS TempEmployer
)AS EmployerName1,
(
SELECT TOP 1 tblExperience.EmployerName
FROM (
SELECT TOP 2 tblExperience.ApplicantExperienceID, tblExperience.EmployerName
FROM (
SELECT TOP 3 tblExperience.ApplicantExperienceID, tblExperience.EmployerName
FROM (
SELECT TOP 4 ApplicantExperienceID, tblExperience.EmployerName
FROM tblExperience
WHERE tblExperience.ApplicationID = 1
ORDER BY tblExperience.ApplicantExperienceID
)AS TempEmployer
ORDER BY tblExperience.ApplicantExperienceID
)AS TempEmployer
ORDER BY tblExperience.ApplicantExperienceID
)AS TempEmployer
ORDER BY tblExperience.ApplicantExperienceID
) AS EmployerName2,
(
SELECT TOP 1 tblExperience.EmployerName
FROM (
SELECT TOP 2 tblExperience.ApplicantExperienceID, tblExperience.EmployerName
FROM (
SELECT TOP 3 tblExperience.ApplicantExperienceID, tblExperience.EmployerName
FROM tblExperience
WHERE tblExperience.ApplicationID = 1
ORDER BY tblExperience.ApplicantExperienceID
)AS TempEmployer
ORDER BY tblExperience.ApplicantExperienceID
)AS TempEmployer
ORDER BY tblExperience.ApplicantExperienceID
)
AS EmployerName3,
(
SELECT TOP 1 tblExperience.EmployerName
FROM (
SELECT TOP 2 tblExperience.ApplicantExperienceID, tblExperience.EmployerName
FROM tblExperience
WHERE tblExperience.ApplicationID = 1
ORDER BY tblExperience.ApplicantExperienceID
)AS TempEmployer
ORDER BY tblExperience.ApplicantExperienceID
)
AS EmployerName4,
(
SELECT TOP 1 tblExperience.EmployerName
FROM tblExperience
WHERE tblExperience.ApplicationID = 1
ORDER BY tblExperience.ApplicantExperienceID
)AS EmployerName5
FROM
tblExperience
WHERE tblExperience.ApplicationID = 1
Ive tried alternating the order statements too but it still returns the exact same results.
It also returns multiple rows which won't work as it must return each row from the experience table as a column in the output.
I hope I have explained my predicament thouroughly enough for someone to understand it and be able to provide some advice.
Thank you in advance...
December 21, 2009 at 11:16 am
warrenstroebel (12/16/2009)
...I hope I have explained my predicament thouroughly enough for someone to understand it and be able to provide some advice. ...
Well, *I* can't figure out what you're asking here, so probably not.
What might help is if you could provide us with some sample data (use INSERT commands so that we can test it ourselves), show us what you are currently getting as output and then show us what you want to get as output.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 24, 2009 at 2:17 pm
I don't know what you're asking, but try the PIVOT command. If the rows you want to convert to columns are dynamic you may need to execute a dynamic sql query.
declare @sql varchar(4000)
set @sql = command --generate whatever cross-tab/pivot command you would like this may be done in multiple lines of code
exec (@sql)
alternatively to using the pivot you can do something similar to the following
select max(my_column_to_row1) [my_column_to_row1]
, max(my_column_to_row2) [my_column_to_row2]
from tablename
Note typically pivots/cross-tabs are used to return multiple rows, but may be used to only return 1 row.
December 28, 2009 at 1:38 pm
Thank you for your replies.
I don't really know how else to state the problem or make it clearer to understand... It is a complicated problem.
I have decided not to extract the data with a single SQL statement and will rather run a query per table and then import the resulting excel spreadsheets into my new DB one by one using the unique ID.
I looked at Pivot tables and FOR loops but they are way too complicated for a novice like me.
Thanks again for the replies.
Cheers
December 28, 2009 at 4:56 pm
warrenstroebel (12/28/2009)
I don't really know how else to state the problem or make it clearer to understand...
Actually, I explained exactly what you could do to make it clearer...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 29, 2009 at 4:35 am
This was removed by the editor as SPAM
December 29, 2009 at 5:02 am
warrenstroebel (12/16/2009)
I have successfully written most of it out in advance...
Warren, does the following query generate the same results as yours, by any chance?
SELECT TOP 1 e.EmployerName AS EmployerName1,
e.EmployerName AS EmployerName2,
e.EmployerName AS EmployerName3,
e.EmployerName AS EmployerName4,
e.EmployerName AS EmployerName5
FROM tblExperience e
WHERE e.ApplicationID = 1
ORDER BY e.ApplicantExperienceID
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply