hi All,
if you remember , long back (3 months back) we were discussing the same issue and it was achived thru stored proc after manipulating some rows and columns the steps. I just forgetting the steps . I think it can be done.. please try.. I will also try...
rgds
Triveni
-----Original Message-----
From: Carol Green
[mailto:mssqldba-ezmlmshield-x54117019.[Email address protected]
Sent: Friday, January 27, 2006 4:55 PM
To: LazyDBA Discussion
Subject: RE: Columns in rows format
I think PIVOT is only available in SQL Server 2005...
-----Original Message-----
From: Albert van Biljon
[mailto:mssqldba-ezmlmshield-x66417405.[Email address protected]
Sent: 27 January 2006 11:19
To: LazyDBA Discussion
Subject: RE: Columns in rows format
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 ===
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
This email and any files transmitted within it are private and confidential.
If you are not the intended recipient, this email and any attachments within it have been transmitted to you in error.
You are prohibited from using, copying, distributing or otherwise using the information contained within this email.
Nothing in this email message amounts to a contractual or legal commitment on the part of Optilan unless confirmed by a communication signed on behalf of the company.
Any email cannot be guaranteed to be secure, error free or free from viruses. Although every possible care is taken by Optilan, Optilan does not accept any liability whatsoever for any loss or damage which may be caused as a result of the transmission of this message by email.
---------------------------------------------------------------------
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.
MS Sql Server LazyDBA home page