how to show row wise data as column wise

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 show row wise data as column wise

Author

Topic

journey
Starting Member

5 Posts

Posted - 2009-08-16 : 12:23:51

Hello everybody,

i need to do a query where i have to show the row data into columns.

Ex:if i have 5 record like this i want to display it as 5 columns

jack
peter
symond
honey
potter

pleas do reply thanks in advance

Regards,
journey

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-16 : 12:41:31

You can use PIVOT operator, if you are using SQL Server 2005 or later.

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

journey
Starting Member

5 Posts

Posted - 2009-08-17 : 02:19:23

could you pleas explain me how to do that??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 02:34:05

Yes, wait a minute. Just have to check Books Online first...........

Here we go!

DECLARE	@Sample TABLE
	(
		Data VARCHAR(20)
	)

INSERT	@Sample
SELECT	'jack' UNION ALL
SELECT	'peter' UNION ALL
SELECT	'symond' UNION ALL
SELECT	'honey' UNION ALL
SELECT	'potter'

SELECT	*
FROM	(
		SELECT	ROW_NUMBER() OVER (ORDER BY Data) AS colID,
			Data
		FROM	@Sample
	) AS s
PIVOT	(
		MAX(Data)
		FOR colID IN ([1], [2], [3], [4], [5])
	) AS p

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

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-17 : 03:20:32

try like this (dynamic crosstab)


select
max(case when colid = 1 then data end ) as '1',
max(case when colid = 2 then data end ) as '2',
max(case when colid = 3 then data end ) as '3',
max(case when colid = 4 then data end ) as '4',
max(case when colid = 5 then data end ) as '5'
from (
		SELECT	ROW_NUMBER() OVER (ORDER BY Data) AS colID,
			Data
		FROM	@Sample
	) AS s

Happy Independence Day..............

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 03:41:33

Please explain to us how that is "dynamic" crosstab?

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

journey
Starting Member

5 Posts

Posted - 2009-08-17 : 04:08:25

THANKS A LOT FOR BOTH OF U AUTHORS

journey
Starting Member

5 Posts

Posted - 2009-08-17 : 05:41:54

im not using sql server trying to do this on oracle database 10g but not getting the output do u have any idea to do the sameitng on 10g database

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 05:50:54

Yes.
Ask over at www.dbforums.com in their ORACLE forum.

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

journey
Starting Member

5 Posts

Posted - 2009-08-18 : 04:04:42

Hi everyone,

can u pleas help for this querry

SELECT SEGMENT1 ,
max( DECODE(SEGMENT1 ,'RAW-PE0-TRP-BLW-040',CNT,NULL))RAW_MAT,
max(DECODE(SEGMENT1,'RAW-MST-BLU-CMN-010',CNT,NULL))MST
FROM(SELECT MTL.SEGMENT1 ,COUNT(*)CNT
FROM MTL_SYSTEM_ITEMS MTL
GROUP BY MTL.SEGMENT1 ) GROUP BY SEGMENT1

Here i want to show the 'RAw-peo-trp' in a seprate column RAW_MAT
'RA-MST-BLU' in a seprate column MST
pleas help me to show the row data into 2 different columns.

im waiting for the legends solution.

Thank you,
Journey.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-18 : 05:24:55

Ask over at www.dbforums.com in their ORACLE forum.
This is a Microsoft SQL Server forum.

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