Re: Columns in rows format

Re: Columns in rows format

 

  

There is a Specific name for this (Columns in rows format)
we call this as* PIVOT* table

Triveni_Gupta wrote:

>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.
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>Website : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>


MS Sql Server LazyDBA home page