How to get rows as columns? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get rows as columns?

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2009-08-04 : 07:31:03

I have the following data.

select, dept_no, emp_no from emp;
10 101
10 103
10 103
20 201

I like to write a simple select to have the following results, which is distinct dept_no with a list of emp_no. Thanks.
10 101, 102, 103
20 201

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 07:45:12

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-04 : 07:49:38

http://www.projectdmx.com/tsql/rowconcatenate.aspx

Vaishu
Posting Yak Master

178 Posts

Posted - 2009-08-04 : 08:05:17

Hi

Thanks guys for rapid response.Sorry din't mention the right format of the result. Could you please tell me how I get the below results(get the results in columns Not seperated by comma(,))

Table
select, dept_no, emp_no from emp;
10 101
10 103
10 103
20 201

Expected results
dept_no--Col1---Col2---Col3
--10-------101----102----103
--20-------201

Advance thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 08:09:28

See http://www.sommarskog.se/dynamic_sql.html#Crosstab

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-04 : 08:34:41

or
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-04 : 09:39:32

quote:
Originally posted by Vaishu

Hi

Thanks guys for rapid response.Sorry din't mention the right format of the result. Could you please tell me how I get the below results(get the results in columns Not seperated by comma(,))

Table
select, dept_no, emp_no from emp;
10 101
10 103
10 103
20 201

Expected results
dept_no--Col1---Col2---Col3
--10-------101----102----103
--20-------201

Advance thanks

will number of values to show be always 3? else you need to use dynamic sql

Vaishu
Posting Yak Master

178 Posts

Posted - 2009-08-04 : 09:51:46

HI

Any one know how to get the below results. Posted replys are not helpful

Table : Tbl1
dept_no----emp_no
--10------- 101
--10--------103
--10--------103
--20--------201

Expected results:

dept_no---Col1---Col2---Col3
--10-------101----103----103
--20-------201

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-04 : 09:55:34

quote:
Originally posted by Vaishu

HI

Any one know how to get the below results. Posted replys are not helpful

Table : Tbl1
dept_no----emp_no
--10------- 101
--10--------103
--10--------103
--20--------201

Expected results:

dept_no---Col1---Col2---Col3
--10-------101----103----103
--20-------201

are you expecting a spoonfeeded answer? why do u think posted answers are not helpful? can we see what you tried?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-04 : 09:56:41

Which version of SQL Server are you using?

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2009-08-04 : 09:56:56

Hi Vishak

It is dynamic... but maximum of 5

quote:
Originally posted by visakh16

quote:
Originally posted by Vaishu

Hi

Thanks guys for rapid response.Sorry din't mention the right format of the result. Could you please tell me how I get the below results(get the results in columns Not seperated by comma(,))

Table
select, dept_no, emp_no from emp;
10 101
10 103
10 103
20 201

Expected results
dept_no--Col1---Col2---Col3
--10-------101----102----103
--20-------201

Advance thanks

will number of values to show be always 3? else you need to use dynamic sql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-04 : 09:57:50

are you using sql 2005?

Vaishu
Posting Yak Master

178 Posts

Posted - 2009-08-04 : 09:58:22

Hi Madhivanan
I am using SQl 2005

quote:
Originally posted by madhivanan

Which version of SQL Server are you using?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 10:05:37

quote:
Originally posted by Vaishu

Posted replys are not helpful

Why not? Because you had to do something useful yourself?

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		DeptNo INT,
		EmpNo INT
	)

INSERT	@Sample
SELECT	10, 101 UNION ALL
SELECT	10, 103 UNION ALL
SELECT	10, 103 UNION ALL
SELECT	20, 201

-- Spoonfeed
SELECT		p.DeptNo,
		p.[1] AS Col1,
		p.[2] AS Col2,
		p.[3] AS Col3,
		p.[4] AS Col4,
		p.[5] AS Col5
FROM		(
			SELECT	DeptNo,
				EmpNo,
				ROW_NUMBER() OVER (PARTITION BY DeptNo ORDER BY EmpNo) AS colID
			FROM	@Sample
		) AS d
PIVOT		(
			MAX(EmpNo)
			FOR colID IN ([1], [2], [3], [4], [5])
		) AS p
ORDER BY	p.DeptNo

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-04 : 10:06:25

[code]SELECT dept_no,
[1] AS Col1,
[2] AS Col2,
[3] AS Col3,
[4] AS Col4,
[5] AS Col5
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY dept_no ORDER BY emp_no) AS Seq,* FROM YourTable)t
PIVOT(MAX(emp_no) FOR Seq IN ([1],[2],[3],[4],[5]))p
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-04 : 10:07:07

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-04 : 10:11:34

and from the procedure from my blog,

EXEC dynamic_pivot 'select dept_no,emp_no from emp','emp_no','max(emp_no)'

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2009-08-04 : 10:36:49

Thanks a Lot

quote:

Originally posted by Peso

quote:
Originally posted by Vaishu

Posted replys are not helpful

Why not? Because you had to do something useful yourself?

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		DeptNo INT,
		EmpNo INT
	)

INSERT	@Sample
SELECT	10, 101 UNION ALL
SELECT	10, 103 UNION ALL
SELECT	10, 103 UNION ALL
SELECT	20, 201

-- Spoonfeed
SELECT		p.DeptNo,
		p.[1] AS Col1,
		p.[2] AS Col2,
		p.[3] AS Col3,
		p.[4] AS Col4,
		p.[5] AS Col5
FROM		(
			SELECT	DeptNo,
				EmpNo,
				ROW_NUMBER() OVER (PARTITION BY DeptNo ORDER BY EmpNo) AS colID
			FROM	@Sample
		) AS d
PIVOT		(
			MAX(EmpNo)
			FOR colID IN ([1], [2], [3], [4], [5])
		) AS p
ORDER BY	p.DeptNo

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources