results from vertical to horizontal order

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

SQL Server 2005 Forums

Transact-SQL (2005)

results from vertical to horizontal order

Author

Topic

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-08 : 09:36:16

hi,

i m getting results in vertical order and i want results in horizontal order.

i m getting results r:

dept deptsec
--------------
maths maths1,maths2

physics phys1,phys2,phys3

biology bio1,bio2

chemistry chem1,chem2,chem3,chem4

i want results like:

maths physics biology chemistry
----------------------------------------
m1,m2 p1,p2,p3 b1,b2 c1,c2,c3,c4

can anyone tell how should i get it in select query?thanks

deepu_v04
Starting Member

3 Posts

Posted - 2008-02-08 : 10:10:08

hi,

you need to write a function or stored procedure to achieve this

try the following code

DECLARE @String varchar(max)

SELECT @STRING = 'SELECT '

declare @col varchar(100)
set @col =''

DECLARE cur CURSOR FOR
SELECT dept FROM t1

OPEN cur
FETCH NEXT FROM cur INTO @col
WHILE @@FETCH_STATUS = 0
BEGIN
set @STRING = @STRING + ' (select '''' + deptsec + '''' from t1 where dept = '''+ @col + ''') AS ' + @COL + ' ,'
FETCH NEXT FROM cur INTO @col
END
CLOSE cur
DEALLOCATE cur
set @string = substring(@string,0,len(@string) - 1)

EXEC (@string)

Sandeep

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources