Results in one row when using an INNER JOIN that returns multiple rows

Question

Post reply

Results in one row when using an INNER JOIN that returns multiple rows

Casper101

Hall of Fame

Points: 3880
More actions
April 16, 2009 at 4:39 am

#131289

Hi,

I am using the Pubs DB as an example to help phrase my question.

Query Example:

SELECT EMPLOYEE.fname, JOBS.job_desc, JOBS.job_id

FROM dbo.employee AS EMPLOYEE

INNER JOIN dbo.jobs AS JOBS

ON EMPLOYEE.job_id = JOBS.job_id

WHERE JOBS.job_desc LIKE 'S%'

ORDER BY JOBS.job_desc

Results:

fname job_desc job_id

Paolo Sale representative 13

Timothy Sale representative 13

Carine Sale representative 13

Is there a way to manipulate the SELECT so the results returns in 1 row and not 3?

Example:

fname1 fname2 fname3 job_desc job_id

Paolo Timothy Carine Sales representative 13

Keeping in mind that I don't alway know how many fname's will be returned for each job description..

Any ideas?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 1

Casper (4/16/2009)

Hi,

I am using the Pubs DB as an example to help phrase my question.

Query Example:

SELECT EMPLOYEE.fname, JOBS.job_desc, JOBS.job_id

FROM dbo.employee AS EMPLOYEE

INNER JOIN dbo.jobs AS JOBS

ON EMPLOYEE.job_id = JOBS.job_id

WHERE JOBS.job_desc LIKE 'S%'

ORDER BY JOBS.job_desc

Results:

fname job_desc job_id

Paolo Sale representative 13

Timothy Sale representative 13

Carine Sale representative 13

Is there a way to manipulate the SELECT so the results returns in 1 row and not 3?

Example:

fname1 fname2 fname3 job_desc job_id

Paolo Timothy Carine Sales representative 13

Keeping in mind that I don't alway know how many fname's will be returned for each job description..

Any ideas?

You can convert rows into Columns but only if you know how many rows you want to convert. In your case You dont know how many Fname's will be returned.

May be with Stored procedure you can write Dynamic SQL's to do it.

But would like to understand why do you want in that way!

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Casper101 Hall of Fame Points: 3880 More actions · Answer 2

The request came in from a client. We provide them with an extract of the data in Excel format and then the records "repeat" as in my example.

They would like to see only 1 record per job title, with all the employees that fall under that job title - all in the same row.

I will never know how many employees fall under each job title - it is never a constant amount. It could be any number. And this number will constantly change as people are reassigned in their position or new people are hired, etc.

So I am at a loss here...

I am thinking creating a table variable will be my best option, where I would INSERT all the DISTINCT job titles, and then have X number of columns for the employee name and UPDATE those afterwards..

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 3

Casper (4/16/2009)

The request came in from a client. We provide them with an extract of the data in Excel format and then the records "repeat" as in my example.

They would like to see only 1 record per job title, with all the employees that fall under that job title - all in the same row.

I will never know how many employees fall under each job title - it is never a constant amount. It could be any number. And this number will constantly change as people are reassigned in their position or new people are hired, etc.

So I am at a loss here...

I am thinking creating a table variable will be my best option, where I would INSERT all the DISTINCT job titles, and then have X number of columns for the employee name and UPDATE those afterwards..

Couple of steps:

1) To find the number of columns you need in table variable or in creating a temporary table, you can execute the below query

SELECT MAX(COUNT(1))

FROM EMPLOYEE

GROUP BY JOB_ID

2) You want to export this data in Excel sheet?? If YES then you can't have more than 256 columns in a sheet. This is a limitation in Excel 2002.

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Check out this excellent article[/url] by Jeff Moden.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 5

Vijaya Kadiyala

SSCrazy Eights

Points: 9119

April 16, 2009 at 10:25 am

#979020

Hi Chris,

tahnk you for giving the link to the article it is very good..and very in-depth...

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers