rows as columns in view - 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

SQL Server 2000 Forums

SQL Server Development (2000)

rows as columns in view

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2005-12-15 : 01:39:27

this might but a stupid question just cant quite remember if it is possible

can i have the rows of my table as columns in my view

i.e
my table =

column 1
row 1
row 2
row 3

my view =
row1 row2 row3

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

sp2 for IE is FIREFOX !!

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-12-15 : 02:03:27

create table #TestView (ROWID int IDENTITY(1,1), EmpNO int, EmpName varchar(10))
insert into #TestView values (101, 'abc')
insert into #TestView values (102, 'pqr')
insert into #TestView values (103, 'xyz')

--select * from #TestView

select RowID, ltrim(str(EmpNo)) as NewCol from #TestView
Union All
select RowID, EmpName as NewCol from #TestView order by Rowid, newcol

Order by will not help while creating view, but this may give you some idea.

Surendra

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-15 : 02:10:00

Use stored procedure

Declare @str varchar(2000)
Select @str=Isnull(@str+' ','')+column1 from yourTable
select @str

Madhivanan

Failing to plan is Planning to fail

Ex
Posting Yak Master

166 Posts

Posted - 2005-12-15 : 17:52:03

surendrakalekar
that doesnt' quite work
it just mergers the two columns not really what i am after

and thanks madhivanan

just wondering is there any where to do this with joins? and not dynamic sql?

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

sp2 for IE is FIREFOX !!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-16 : 00:37:02

You should use Dynamic SQL

Madhivanan

Failing to plan is Planning to fail

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-12-16 : 01:09:35

Or use CSV format output in views. And process that CSV format output in some sp/front-end.

Surendra

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources