How to extract multiple rows in a single query

Question

Post reply

How to extract multiple rows in a single query

Niladri Saha

SSC Veteran

Points: 298
More actions
July 14, 2007 at 5:24 am

#173453

I have two tables.

1 Employee table contians Employee_id and Employee_name

2.EmpSalary contains Employee_id and SalaryMonth

Please see the data representation in these tables.

Employee:

============

Employee_id Employee_Name

=====================================

1 Niladri Saha

2 Pallab Roy

EmpSalary:

============

Employee_id SalaryMonth

=====================================

1 January

1 February

1 March

1 April

2 January

2 February

I want output like the following

Employee Id Employee Name Salary Month

===========================================================

1 Niladri Saha January, February,March, April

2 Pallab Roy January, February

I can acheive the above results by wrtiing a cursor but I want to achieve the above by writing a select statement. Is it possible?

Please provide me solution only related to SQL Server.

Viewing 10 posts - 1 through 9 (of 9 total)

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

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 1

SwePeso

SSC-Dedicated

Points: 39725

July 14, 2007 at 7:27 am

#719483

You can write a function that gets all the salary months for you and combine them.

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Uh... what happens when you have more than 1 year? In other words, why are you storing the salary month and not a salary date/time so you can do multiple different calcs?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 3

SwePeso

SSC-Dedicated

Points: 39725

July 14, 2007 at 12:36 pm

#719493

I "think" he has one table for each year...

N 56°04'39.16"
E 12°55'05.25"

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 4

ChrisTaylor

SSCarpal Tunnel

Points: 4527

July 16, 2007 at 1:47 am

#719575

can ya not use a CASE statement???

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

Christopher Angelo G. Ayroso SSC Rookie Points: 34 More actions · Answer 5


if  exists (select name from sys.databases where name = N'narraSoft_DB')
begin
	drop database narraSoft_DB
end
go

create database narraSoft_DB
go

use narraSoft_DB
go

create table Employee
(
	employee_id int identity(1,1) constraint employee_pk primary key clustered,
	employee_name nvarchar(200)
)
go

--	dummy data
insert into Employee(employee_name)
	values	('Niladri Saha')
insert into Employee(employee_name)
	values	('Pallab Roy')

select * from Employee
go


create table Employee_Salary
(
	employee_id int not null,
	salary_month varchar(20) not null, -- is supposed to be a string?
	constraint employee_salary_employee_pk foreign key(employee_id) references Employee(employee_id),
)
go

--	dummy data
insert into Employee_Salary(employee_id,salary_month)
	values	(1,'January')
insert into Employee_Salary(employee_id,salary_month)
	values	(1,'February')
insert into Employee_Salary(employee_id,salary_month)
	values	(1,'March')
insert into Employee_Salary(employee_id,salary_month)
	values	(1,'April')
insert into Employee_Salary(employee_id,salary_month)
	values	(2,'January')
insert into Employee_Salary(employee_id,salary_month)
	values	(2,'February')

select * from Employee_Salary
go


select	emp.employee_id
	, emp.employee_name
	, ISNULL((select *
			from (
				select	cast(salary_month as varchar(max)) + ',' as [text()]
				from	Employee_Salary es
				where	es.employee_id=emp.employee_id
				)tmp
			for xml path('')
		),',') as 'salary_month'
from	Employee emp
go


use master
go

-------------------------------------------------------------------------------

try this on your query analyzer, mate.. 

this uses the XML Path to concatenate the Employee_Salary table
while retrieving the records of the Employee table

the only prob with this is the trailing comma(,)..
but the client app may remove that for us, instead in the server..

if there is any question, comment or any suggestion from you mates,
just email me and they are
surely be appreciated..

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 6

SwePeso

SSC-Dedicated

Points: 39725

July 16, 2007 at 9:37 am

#719716

FOR XML PATH is available in SQL Server 2000?

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Heh... not according to BOL which states (no sign of PATH anywhere)...

Basic Syntax of the FOR XML Clause

The basic syntax for specifying the XML mode in the FOR clause is:

FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE64]

Arguments

XML mode

Specifies the XML mode. XML mode determines the shape of the resulting XML.

mode can be RAW, AUTO, or EXPLICIT.

XMLDATA

Specifies that an XML-Data schema should be returned. The schema is prepended to the document as an inline schema.

ELEMENTS

If the ELEMENTS option is specified, the columns are returned as subelements. Otherwise, they are mapped to XML attributes. This option is supported in AUTO mode only.

BINARY BASE64

If the BINARY Base64 option is specified, any binary data returned by the query is represented in base64-encoded format. To retrieve binary data using RAW and EXPLICIT mode, this option must be specified. In AUTO mode, binary data is returned as a reference by default.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Niladri Saha SSC Veteran Points: 298 More actions · Answer 8

Hi,

Still I den't get any solution from this forum but still thx for your time tospend for this query. Find the solution below.

create function dbo.getSalaryMonth(@Empid int) returns varchar(1000)

as

begin

declare @a varchar(1000)

select @a = coalesce(@a, '') + ', ' + coalesce(SalaryMonth, '')

from dbo.EmpSalary

where [employee_id] = @Empid

return substring(@a, 3, 1000)

end

go

SELECT employee_id,employee_Name, dbo.getSalaryMonth(employee_id) as [Salary Month]

FROM dbo.Employee

Niladri Saha SSC Veteran Points: 298 More actions · Answer 9

Hi,

Still I den't get any solution from this forum but still thx for your time tospend for this query. Find the solution below.

create function dbo.getSalaryMonth(@Empid int) returns varchar(1000)

as

begin

declare @a varchar(1000)

select @a = coalesce(@a, '') + ', ' + coalesce(SalaryMonth, '')

from dbo.EmpSalary

where [employee_id] = @Empid

return substring(@a, 3, 1000)

end

go

SELECT employee_id,employee_Name, dbo.getSalaryMonth(employee_id) as [Salary Month]

FROM dbo.Employee