Hi Triveni,

So you need to pivot the table... I don't know the SQL
for that, since it isn't quite straightforward as far
as I know. If you search the archives, you'll find
some posting on this list, you'll find a posting with
a stored procedure to do it.

Regards,
Albert

--- Triveni_Gupta
<mssqldba-ezmlmshield-x27329880.[Email address protected]
wrote:

>
> please look at the output
>
>
> select * from r1
>
> create table r1
> (
> name1 varchar(30),
> roll int,
> address varchar(30))
>
> nsert into r1 values('triveni','1','adadd')
> insert into r1 values('arun','3','dfd')
> insert into r1 values('deepa','4','fsfsf')
> insert into r1 values('purna','5','fsfs')
> insert into r1 values('gaya','6','fsfs')
> insert into r1 values('milan','7','fsfs')
>
> alter view col
> as
> select CONVERT (varchar (255), name1) AS name1 from
> r1
> union
> select CONVERT (varchar (255), roll) AS roll from r1
> union
> select CONVERT (varchar (255), address) AS address
> from r1
>
> select * from col
>
>
> But I want in to be in below way
>
>
> col1 col2 col3
> name1 triveni arun
> roll 1 2
> address adadd dfd
>
>
>
>
> -----Original Message-----
> From: Albert van Biljon
> [mailto:mssqldba-ezmlmshield-x74773300.[Email
> address protected]
> Sent: Friday, January 27, 2006 4:34 PM
> To: LazyDBA Discussion
> Subject: RE: Columns in rows format
>
>
> Hi Triveni,
>
> Perhaps you would want to try something like this:
>
> SELECT CONVERT (varchar (255), IntColumnName) AS
> [SomeColumnName]
>
> UNION
>
> SELECT CONVERT (varchar (255), Str30ColumnName) AS
> [SomeColumnName]
>
> UNION
>
> SELECT CONVERT (varchar (255), DateColumnName) AS
> [SomeColumnName]
>
> Regards,
> Albert
>
> (Remember to reply to all, if you want to send to
> the
> list too)
>
> --- Triveni_Gupta <[Email address protected] wrote:
>
> > Does not work, selecting individual columns in
> each
> > select throws error in converting datatype,
> > other way if I do select * from table and then
> union
> > them, giving same resutls. any other help
> >
> > -----Original Message-----
> > From: Albert van Biljon
> > [mailto:[Email address protected]
> > Sent: Friday, January 27, 2006 4:22 PM
> > To: Triveni_Gupta
> > Subject: Re: Columns in rows format
> >
> >
> > **A LazyDBA.com subscriber has responded to your
> > lazydba.com post**
> > **LazyDBA.com mail shield has forwarded you this
> > email,
> > **and removed any attachments, and kept your email
> > address secret
> > **from this person, and any viruses/trojans.
> > **If you reply to this email, the person will see
> > your email address as normal
> > **Anything below this line is the original email
> > text
> >
> >
> > Hi Triveni,
> >
> > You can do 5 separate selects and UNION them.
> >
> > Regards,
> > Albert
> >
> >
> > --- Triveni_Gupta
> > [Email address protected]
> > wrote:
> >
> > > Hi Experts
> > >
> > > I need my columns in rows format. I have 5
> columns
> > > and want it to be shown as rows in QA window.
> > > Any help would be highly appreciated.
> > >
> > > Thanks in advance.
> > >
> > > Cheers
> > > Triveni
> > >
> > >
> > >
> >
>
---------------------------------------------------------------------
> > > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL,
> NOT
> > > JUST REPLY
> > > Website : http://www.LazyDBA.com
> > > To unsubscribe:
> > > http://www.lazydba.com/unsubscribe.html
> > >
> > >
> > >
> > >
> > > DISCLAIMER:
> > > This email (including any attachments) is
> intended
> > > for the sole use of the intended recipient/s and
> > may
> > > contain material that is CONFIDENTIAL AND
> PRIVATE
> > > COMPANY INFORMATION. Any review or reliance by
> > > others or copying or distribution or forwarding
> of
> > > any or all of the contents in this message is
> > > STRICTLY PROHIBITED. If you are not the intended
> > > recipient, please contact the sender by email
> and
> > > delete all copies; your cooperation in this
> regard
> > > is appreciated.
> > >
> > >
> > >
> > >
> >
>
---------------------------------------------------------------------
> > > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL,
> NOT
> > > JUST REPLY
> > > Website : http://www.LazyDBA.com
> > > To unsubscribe:
> > > http://www.lazydba.com/unsubscribe.html
> > >
> > >
> >
> >
> >
> >
> >
> >
> > DISCLAIMER:
> > This email (including any attachments) is intended
> > for the sole use of the intended recipient/s and
> may
> > contain material that is CONFIDENTIAL AND PRIVATE
> > COMPANY INFORMATION. Any review or reliance by
> > others or copying or distribution or forwarding of
> > any or all of the contents in this message is
> > STRICTLY PROHIBITED. If you are not the intended
> > recipient, please contact the sender by email and
> > delete all copies; your cooperation in this regard
> > is appreciated.
> >
> >
>
>
>
>
---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT
> JUST REPLY
>
=== message truncated ===


MS Sql Server LazyDBA home page