oh Thanks a bunch!!! carol
but how will I insert the data from the main table: like if my r1 table looks like below structure.
r1
triveni 1 adadd
arun 3 dfd
deepa 4 fsfsf
purna 5 fsfs
gaya 6 fsfs
milan 7 fsfs
#temp
col1 col2 col3 col4 col5 col6 col7
addressNULL NULL NULL NULL NULL NULL
name1 NULL NULL NULL NULL NULL NULL
roll NULL NULL NULL NULL NULL NULL
Rgds
Triveni
-----Original Message-----
From: Carol Green
[mailto:mssqldba-ezmlmshield-x5059008.[Email address protected]
Sent: Friday, January 27, 2006 7:36 PM
To: LazyDBA Discussion
Subject: RE: Columns in rows format
I've got to go now but this might be a start to you:
DECLARE @Table nvarchar(50), @NumCols int, @NumRows int, @n int, @strSQL
nvarchar(300)
--DROP TABLE #Temp
Set @Table = 'r1'
Set @n = 0
Set @NumCols = (SELECT COUNT(*) FROM r1)
Set @NumRows = (SELECT COUNT(*) FROM syscolumns WHERE ID = (SELECT ID
FROM sysobjects WHERE name = @Table))
CREATE TABLE #Temp(
Col1 nvarchar(50)
)
INSERT INTO #Temp SELECT [Name] FROM syscolumns WHERE ID = (SELECT ID
FROM sysobjects WHERE name = @Table)
WHILE @n < @NumCols
BEGIN
Set @strSQL = 'ALTER TABLE #Temp ADD Col' + CONVERT(nvarchar(10),@n+2) +
' nvarchar(50) NULL'
PRINT (@strSQL)
EXEC (@strSQL)
Set @n = @n + 1
END
GO
SELECT * FROM #Temp
-----Original Message-----
From: Triveni_Gupta
[mailto:mssqldba-ezmlmshield-x27329880.[Email address protected]
Sent: 27 January 2006 11:09
To: LazyDBA Discussion
Subject: RE: Columns in rows format
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 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
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