How to show data of multiple rows to a single row

Question

Viewing 4 posts - 1 through 3 (of 3 total)

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

a20213 Say Hey Kid Points: 664 More actions · Answer 1

does it help this way ?

declare @employee table

(

emp_name varchar(25),

emp_id int,

dept_no int,

emp_sal int

)

insert @employee (emp_name, emp_id, dept_no, emp_sal)

select 'Ranit', 1001, 10, 10000

union

select 'Rahul', 1002, 20, 10500

union

select 'Rajat', 1003, 30, 10000

union

select 'Ravi', 1004, 10, 10020

union

select 'Ridhima', 1005, 10, 10900

union

select 'Raj', 1006, 30, 11110

union

select 'Ranjan', 1007, 40, 10230

union

select 'Ranita', 1008, 40, 10220

union

select 'Rasika', 1009, 20, 10010

union

select 'Rajen', 1010, 20, 10030

--select [emp_name], [emp_id], [dept_no], [emp_sal] from @employee

SELECT DISTINCT EMP2.dept_no,

SUBSTRING((select ','+ EMP1.emp_name + ' ' + CAST(EMP1.emp_id as nvarchar(max)) + ' ' + CAST(EMP1.emp_sal as nvarchar(max)) as [text()]

from ( select distinct [emp_name], [emp_id], [dept_no], [emp_sal] FROM @employee ) EMP1

where EMP1.dept_no = EMP2.dept_no

ORDER BY EMP1.dept_no

For XML PATH ('')),2, 1000) [List]

FROM ( SELECT DISTINCT [emp_name], [emp_id], [dept_no], [emp_sal] FROM @employee ) EMP2

Sean Lange SSC Guru Points: 286562 More actions · Answer 2

Take a look at the articles in my signature about cross tabs. I suspect you will need to use the dynamic version here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

debadrita.2.datta Newbie Points: 2 More actions · Answer 3

Thanks. I used pivot to get the required details. Thanks for your help.