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
Arunkumar
On 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
|||"Oonz" <arundhaj@.gmail.com> wrote in message
news:1180432391.202436.272930@.q69g2000hsb.googlegr oups.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
>
>