Sql Stop Logging: transpose

Showing posts with label transpose. Show all posts

Friday, March 30, 2012

How to transpose the columns into rows

Hi Friends,
How to transpose the columns into rows
i want to convert the table which looks like this
ID Name HomePhone WorkPhone Email
1 test1 678364 643733 test1@.test.com
2 test2 678344 643553 test2@.test.com
to a table which should look like this
ID Name Device
1 test1 678364
1 test1 643733
1 test1 test1@.test.com
2 test2 678344
2 test2 643553
2 test2 test2@.test.com
Thanks in Advance
ArunkumarOn May 29, 2:53 pm, Oonz <arund...@.gmail.com> wrote:
> Hi Friends,
> How to transpose the columns into rows
> i want to convert the table which looks like this
> ID Name HomePhone WorkPhone Email
> 1 test1 678364 643733 t...@.test.com
> 2 test2 678344 643553 t...@.test.com
> to a table which should look like this
> ID Name Device
> 1 test1 678364
> 1 test1 643733
> 1 test1 t...@.test.com
> 2 test2 678344
> 2 test2 643553
> 2 test2 t...@.test.com
> Thanks in Advance
> Arunkumar
One of the ways
select id,name,homephone as device
from table
union all
select id,name,workphone as device
from table
union all
select id,name,email as device
from table|||Hi
here you go
select id,name,homephone as device
from table
union all
select id,name,workphone as device
from table
union all
select id,name,email as device
from table
see this link on How to rotate a table in SQL Server
http://support.microsoft.com/defaul...b;EN-US;q175574
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Oonz" <arundhaj@.gmail.com> wrote in message
news:1180432391.202436.272930@.q69g2000hsb.googlegroups.com...
> Hi Friends,
>
> How to transpose the columns into rows
> i want to convert the table which looks like this
> ID Name HomePhone WorkPhone Email
> 1 test1 678364 643733 test1@.test.com
> 2 test2 678344 643553 test2@.test.com
>
> to a table which should look like this
> ID Name Device
> 1 test1 678364
> 1 test1 643733
> 1 test1 test1@.test.com
> 2 test2 678344
> 2 test2 643553
> 2 test2 test2@.test.com
>
> Thanks in Advance
> Arunkumar
>|||"Oonz" <arundhaj@.gmail.com> wrote in message
news:1180432391.202436.272930@.q69g2000hsb.googlegroups.com...
> Hi Friends,
>
> How to transpose the columns into rows
What version of SQL Server are you on?
If SQL 2005, in addition to the methods suggested, look up the PIVOT
command.

> i want to convert the table which looks like this
> ID Name HomePhone WorkPhone Email
> 1 test1 678364 643733 test1@.test.com
> 2 test2 678344 643553 test2@.test.com
>
> to a table which should look like this
> ID Name Device
> 1 test1 678364
> 1 test1 643733
> 1 test1 test1@.test.com
> 2 test2 678344
> 2 test2 643553
> 2 test2 test2@.test.com
>
> Thanks in Advance
> Arunkumar
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Greg, you probably meant UNPIVOT

SELECT ID, Name, Device
FROM Foobar
UNPIVOT
(Device FOR DeviceType IN
(HomePhone, WorkPhone, Email)) AS U;
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||No no no.. I meant Pivot... because of course reading about PIVOT will lead
to UNPIVOT.
Yeah... that's it. That's what I meant :-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:eq6Qr7eoHHA.3460@.TK2MSFTNGP04.phx.gbl...
> Greg, you probably meant UNPIVOT

> SELECT ID, Name, Device
> FROM Foobar
> UNPIVOT
> (Device FOR DeviceType IN
> (HomePhone, WorkPhone, Email)) AS U;
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
>

How to transpose the columns into rows

SELECT ID, Name, Device
FROM Foobar
UNPIVOT
(Device FOR DeviceType IN
(HomePhone, WorkPhone, Email)) AS U;
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||No no no.. I meant Pivot... because of course reading about PIVOT will lead
to UNPIVOT.
Yeah... that's it. That's what I meant :-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:eq6Qr7eoHHA.3460@.TK2MSFTNGP04.phx.gbl...
> Greg, you probably meant UNPIVOT

> SELECT ID, Name, Device
> FROM Foobar
> UNPIVOT
> (Device FOR DeviceType IN
> (HomePhone, WorkPhone, Email)) AS U;
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
>

How to transpose the columns into rows

Hi Friends,
How to transpose the columns into rows
i want to convert the table which looks like this
ID Name HomePhone WorkPhone Email
1 test1 678364 643733 test1@.test.com
2 test2 678344 643553 test2@.test.com
to a table which should look like this
ID Name Device
1 test1 678364
1 test1 643733
1 test1 test1@.test.com
2 test2 678344
2 test2 643553
2 test2 test2@.test.com
Thanks in Advance
ArunkumarOn May 29, 2:53 pm, Oonz <arund...@.gmail.com> wrote:
> Hi Friends,
> How to transpose the columns into rows
> i want to convert the table which looks like this
> ID Name HomePhone WorkPhone Email
> 1 test1 678364 643733 t...@.test.com
> 2 test2 678344 643553 t...@.test.com
> to a table which should look like this
> ID Name Device
> 1 test1 678364
> 1 test1 643733
> 1 test1 t...@.test.com
> 2 test2 678344
> 2 test2 643553
> 2 test2 t...@.test.com
> Thanks in Advance
> Arunkumar
One of the ways
select id,name,homephone as device
from table
union all
select id,name,workphone as device
from table
union all
select id,name,email as device
from table|||Hi
here you go
select id,name,homephone as device
from table
union all
select id,name,workphone as device
from table
union all
select id,name,email as device
from table
see this link on How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175574
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Oonz" <arundhaj@.gmail.com> wrote in message
news:1180432391.202436.272930@.q69g2000hsb.googlegroups.com...
> Hi Friends,
>
> How to transpose the columns into rows
> i want to convert the table which looks like this
> ID Name HomePhone WorkPhone Email
> 1 test1 678364 643733 test1@.test.com
> 2 test2 678344 643553 test2@.test.com
>
> to a table which should look like this
> ID Name Device
> 1 test1 678364
> 1 test1 643733
> 1 test1 test1@.test.com
> 2 test2 678344
> 2 test2 643553
> 2 test2 test2@.test.com
>
> Thanks in Advance
> Arunkumar
>|||"Oonz" <arundhaj@.gmail.com> wrote in message
news:1180432391.202436.272930@.q69g2000hsb.googlegroups.com...
> Hi Friends,
>
> How to transpose the columns into rows
What version of SQL Server are you on?
If SQL 2005, in addition to the methods suggested, look up the PIVOT
command.
> i want to convert the table which looks like this
> ID Name HomePhone WorkPhone Email
> 1 test1 678364 643733 test1@.test.com
> 2 test2 678344 643553 test2@.test.com
>
> to a table which should look like this
> ID Name Device
> 1 test1 678364
> 1 test1 643733
> 1 test1 test1@.test.com
> 2 test2 678344
> 2 test2 643553
> 2 test2 test2@.test.com
>
> Thanks in Advance
> Arunkumar
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Greg, you probably meant UNPIVOT :)
SELECT ID, Name, Device
FROM Foobar
UNPIVOT
(Device FOR DeviceType IN
(HomePhone, WorkPhone, Email)) AS U;
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||No no no.. I meant Pivot... because of course reading about PIVOT will lead
to UNPIVOT.
Yeah... that's it. That's what I meant :-)
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:eq6Qr7eoHHA.3460@.TK2MSFTNGP04.phx.gbl...
> Greg, you probably meant UNPIVOT :)
> SELECT ID, Name, Device
> FROM Foobar
> UNPIVOT
> (Device FOR DeviceType IN
> (HomePhone, WorkPhone, Email)) AS U;
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
>

how to transpose col values into row values?

Hello SQL people,

I need a way to transpose the values of a column into values in a row in a SqlServer7 table. Here is the problem:

I have a table employeeattendance.
Here is the script -

if exists (select * from sysobjects where id = object_id(N'[dbo].[EmployeeAttendance]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EmployeeAttendance]
GO

CREATE TABLE [dbo].[EmployeeAttendance] (
[EmployeeID] [varchar] (20) NULL ,
[AttendanceDate] [varchar] (10) NULL ,
[Status] [char] (1) NULL
) ON [PRIMARY]
GO

insert into [EmployeeAttendance] Values ( '20001', '2003-03-28' , 'Y')
insert into [EmployeeAttendance] Values ( '20001', '2003-03-29' , 'N')
insert into [EmployeeAttendance] Values ( '20001', '2003-03-30' , 'N')
insert into [EmployeeAttendance] Values ( '20002', '2003-03-28' , 'Y')
insert into [EmployeeAttendance] Values ( '20002', '2003-03-29' , 'N')

Now if i say
select * from employeeattendance
Output is -
EmployeeID AttendanceDate Status
20001 2003-03-28 Y
20001 2003-03-29 N
20001 2003-03-30 N
20002 2003-03-28 Y
20002 2003-03-29 N

But i want the output some thing like this-

20001 2003-03-28 Y 2003-03-29 N 2003-03-30 N
20002 2003-03-28 Y 2003-03-29 N

Please, any help would be greatly appreciated. Thanks, GolaI had answer a similar question before. See my replay at view and link table thread on 14 March 2003

ionut|||Originally posted by ionut calin
I had answer a similar question before. See my replay at view and link table thread on 14 March 2003

ionut

Thanks ionut for reply, but i am using MS sql server 7.0 and i have only one table.

Thanks once again.

Gola Munjal|||you could do it in your application program, or else use transact-sql

see
http://sqlteam.com/item.asp?ItemID=11021
and
http://sqlteam.com/item.asp?ItemID=2368

rudy

how to transpose a table using sql statement

Just curious: why do you want to do this?
What kind of software environment do you work in?"kei" <kei@.discussions.microsoft.com> wrote in message
news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
If you're using SQL 2005, look up pivot()

> as caption, I want to use sql statement to transponse a table (row to
column
> and column to row), is this possible and how to script it?
> e.g.
> 1,2,3
> 4,5,6
> (1,2,3) is column name
> after transponse, the table become
> 1,4
> 2,5
> 3,6
> (1,4) is column name
> Thx!!|||Oh! I am using SQL2000,is there any method using SQL Server 2000?
"Greg D. Moore (Strider)" wrote:

> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
> If you're using SQL 2005, look up pivot()
>
> column
>
>|||With SQL Server 2000, you do it with a GROUP BY clause and a series of
<AggregateFunction>(CASE...) columns in the select list, where
AggregateFunction is SUM() or MAX() or MIN(), etc. depending on your needs.
See http://www.aspfaq.com/2462
*mike hodgson*
http://sqlnerd.blogspot.com
kei wrote:
[vbcol=seagreen]
>Oh! I am using SQL2000,is there any method using SQL Server 2000?
>"Greg D. Moore (Strider)" wrote:
>
>|||as caption, I want to use sql statement to transponse a table (row to column
and column to row), is this possible and how to script it?
e.g.
1,2,3
4,5,6
(1,2,3) is column name
after transponse, the table become
1,4
2,5
3,6
(1,4) is column name
Thx!!|||"kei" <kei@.discussions.microsoft.com> wrote in message
news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
If you're using SQL 2005, look up pivot()

> as caption, I want to use sql statement to transponse a table (row to
column
> and column to row), is this possible and how to script it?
> e.g.
> 1,2,3
> 4,5,6
> (1,2,3) is column name
> after transponse, the table become
> 1,4
> 2,5
> 3,6
> (1,4) is column name
> Thx!!|||Oh! I am using SQL2000,is there any method using SQL Server 2000?
"Greg D. Moore (Strider)" wrote:

> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
> If you're using SQL 2005, look up pivot()
>
> column
>
>|||With SQL Server 2000, you do it with a GROUP BY clause and a series of
<AggregateFunction>(CASE...) columns in the select list, where
AggregateFunction is SUM() or MAX() or MIN(), etc. depending on your needs.
See http://www.aspfaq.com/2462
*mike hodgson*
http://sqlnerd.blogspot.com
kei wrote:
[vbcol=seagreen]
>Oh! I am using SQL2000,is there any method using SQL Server 2000?
>"Greg D. Moore (Strider)" wrote:
>
>|||Just curious: why do you want to do this?
What kind of software environment do you work in?|||may be i can help you in this regard.
can u publish some sample data with scripts to create object?
kay
"kei" <kei@.discussions.microsoft.com> wrote in message
news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
> as caption, I want to use sql statement to transponse a table (row to
> column
> and column to row), is this possible and how to script it?
> e.g.
> 1,2,3
> 4,5,6
> (1,2,3) is column name
> after transponse, the table become
> 1,4
> 2,5
> 3,6
> (1,4) is column name
> Thx!!sql

how to transpose a table using sql statement

as caption, I want to use sql statement to transponse a table (row to column
and column to row), is this possible and how to script it?
e.g.
1,2,3
4,5,6
(1,2,3) is column name
after transponse, the table become
1,4
2,5
3,6
(1,4) is column name
Thx!!"kei" <kei@.discussions.microsoft.com> wrote in message
news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
If you're using SQL 2005, look up pivot()
> as caption, I want to use sql statement to transponse a table (row to
column
> and column to row), is this possible and how to script it?
> e.g.
> 1,2,3
> 4,5,6
> (1,2,3) is column name
> after transponse, the table become
> 1,4
> 2,5
> 3,6
> (1,4) is column name
> Thx!!|||Oh! I am using SQL2000,is there any method using SQL Server 2000?
"Greg D. Moore (Strider)" wrote:
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
> If you're using SQL 2005, look up pivot()
>
> > as caption, I want to use sql statement to transponse a table (row to
> column
> > and column to row), is this possible and how to script it?
> > e.g.
> > 1,2,3
> > 4,5,6
> > (1,2,3) is column name
> > after transponse, the table become
> > 1,4
> > 2,5
> > 3,6
> > (1,4) is column name
> >
> > Thx!!
>
>|||This is a multi-part message in MIME format.
--020008060109080504060608
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
With SQL Server 2000, you do it with a GROUP BY clause and a series of
<AggregateFunction>(CASE...) columns in the select list, where
AggregateFunction is SUM() or MAX() or MIN(), etc. depending on your needs.
See http://www.aspfaq.com/2462
--
*mike hodgson*
http://sqlnerd.blogspot.com
kei wrote:
>Oh! I am using SQL2000,is there any method using SQL Server 2000?
>"Greg D. Moore (Strider)" wrote:
>
>>"kei" <kei@.discussions.microsoft.com> wrote in message
>>news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
>>If you're using SQL 2005, look up pivot()
>>
>>
>>as caption, I want to use sql statement to transponse a table (row to
>>
>>column
>>
>>and column to row), is this possible and how to script it?
>>e.g.
>>1,2,3
>>4,5,6
>>(1,2,3) is column name
>>after transponse, the table become
>>1,4
>>2,5
>>3,6
>>(1,4) is column name
>>Thx!!
>>
>>
--020008060109080504060608
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>With SQL Server 2000, you do it with a GROUP BY clause and a series
of <AggregateFunction>(CASE...) columns in the select list, where
AggregateFunction is SUM() or MAX() or MIN(), etc. depending on your
needs. 
 
See <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.aspfaq.com/2462</a></tt> ">http://www.aspfaq.com/2462">http://www.aspfaq.com/2462</a></tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
kei wrote:
<blockquote cite="mid723F76A5-9F57-42B1-B787-DF454C95361D@.microsoft.com"
type="cite">
<pre wrap="">Oh! I am using SQL2000,is there any method using SQL Server 2000?
"Greg D. Moore (Strider)" wrote:
</pre>
<blockquote type="cite">
<pre wrap="">"kei" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:kei@.discussions.microsoft.com"><kei@.discussions.microsoft.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com">news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com</a>...
If you're using SQL 2005, look up pivot()
</pre>
<blockquote type="cite">
<pre wrap="">as caption, I want to use sql statement to transponse a table (row to
</pre>
</blockquote>
<pre wrap="">column
</pre>
<blockquote type="cite">
<pre wrap="">and column to row), is this possible and how to script it?
e.g.
1,2,3
4,5,6
(1,2,3) is column name
after transponse, the table become
1,4
2,5
3,6
(1,4) is column name
Thx!!
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</blockquote>
</body>
</html>
--020008060109080504060608--|||Just curious: why do you want to do this?
What kind of software environment do you work in?|||may be i can help you in this regard.
can u publish some sample data with scripts to create object?
kay
"kei" <kei@.discussions.microsoft.com> wrote in message
news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
> as caption, I want to use sql statement to transponse a table (row to
> column
> and column to row), is this possible and how to script it?
> e.g.
> 1,2,3
> 4,5,6
> (1,2,3) is column name
> after transponse, the table become
> 1,4
> 2,5
> 3,6
> (1,4) is column name
> Thx!!

Friday, March 30, 2012

How to transpose the columns into rows

How to transpose the columns into rows

How to transpose the columns into rows

how to transpose col values into row values?

how to transpose a table using sql statement

how to transpose a table using sql statement

Sql Stop Logging

Blog Archive

About Me