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

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
>
>

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 tranfer excel data to sqlserver2000 ?

In our project we r having a task such that to convert the excel data to sqlserver2000 . what is the procedure ? (Bulk amount of data )

Try the thread below for code to create a linked server with Excel. Hope this helps.
http://forums.asp.net/926047/ShowPost.aspx|||If you want to convert excel data permanently into SQL server
Right Click database -> All Task -> Import data ...-> Choose data source to be Excel, specify file path, choose destination, new table name .. map columns if necessary... just follow the wizard.
Happy programming!

How to tell if I can convert a varchar to int

Hi,
I am attempting to write a procedure whereby I can get the maximum numeric
value in a varchar column (which may contain a mix of words and number
strings).
I'm trying to use:
SELECT MAX ( case isnumeric(value) when 1 then cast (value as int) else 0
end ) FROM table
However, given that entries in the value column can be up to 255 characters,
I obviously have a problem when the number is too long to be cast as an int
(or indeed a bigint or whatever).
In the case of this happening, I'd like to ignore these values, so what I
need is a means of telling whether a value can be cast as an int before
attempting to do it. Using isnumeric(..) isn't enough because it can't
confirm that I can actually convert it.
Any help would be very welcome,
Thanks in advance,
Chris.Maybe try datalength(column) which gives you the number of characters...
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
news:LnD3c.3274$re1.2931@.newsfe1-win...
> Hi,
> I am attempting to write a procedure whereby I can get the maximum numeric
> value in a varchar column (which may contain a mix of words and number
> strings).
> I'm trying to use:
> SELECT MAX ( case isnumeric(value) when 1 then cast (value as int) else 0
> end ) FROM table
> However, given that entries in the value column can be up to 255
characters,
> I obviously have a problem when the number is too long to be cast as an
int
> (or indeed a bigint or whatever).
> In the case of this happening, I'd like to ignore these values, so what I
> need is a means of telling whether a value can be cast as an int before
> attempting to do it. Using isnumeric(..) isn't enough because it can't
> confirm that I can actually convert it.
> Any help would be very welcome,
> Thanks in advance,
> Chris.
>|||To add to Wayne's response, consider using LIKE instead of ISNUMERIC. The
ISNUMERIC function will return 1 for some obscure numeric expressions.
SELECT
MAX(CAST(Value as decimal(38)))
FROM MyTable
WHERE
Value NOT LIKE '%[^0-9]%' AND
DATALENGTH(Value) < 39 AND
Value <> ''
Also, the need to do this sort of thing may be symptomatic of a database
design issue. It's usually not a good idea to store different types of data
in the same column in a relational database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
news:LnD3c.3274$re1.2931@.newsfe1-win...
> Hi,
> I am attempting to write a procedure whereby I can get the maximum numeric
> value in a varchar column (which may contain a mix of words and number
> strings).
> I'm trying to use:
> SELECT MAX ( case isnumeric(value) when 1 then cast (value as int) else 0
> end ) FROM table
> However, given that entries in the value column can be up to 255
characters,
> I obviously have a problem when the number is too long to be cast as an
int
> (or indeed a bigint or whatever).
> In the case of this happening, I'd like to ignore these values, so what I
> need is a means of telling whether a value can be cast as an int before
> attempting to do it. Using isnumeric(..) isn't enough because it can't
> confirm that I can actually convert it.
> Any help would be very welcome,
> Thanks in advance,
> Chris.
>

how to sum up this thing?

hi guys.
in my select statement, i have this
CASE WHEN CONVERT(int,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) IS NULL THEN '0' ELSE CONVERT(varchar,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) END AS AmountPaid

the problem that i faced is, i need to sum up another time according to the year. the above statement will shoe the amount paid. i need to sum up the amount for each year. i've tried to use sum function, but it gives me error. Please help me to solve this problem. Thanks for all advise. I would appreciate it very much.

The simplified query....

(SELECT Convert(varchar,ISNULL(SUM(TC.amount),0)) FROM tb_payment AS TC
WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL) as AmountPaid

Here you can use join rather subquery, if you post the full query i may help you to tune..

|||
SELECT TOP 5 tr.year, CASE WHEN CONVERT(int,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) IS NULL THEN '0' ELSE CONVERT(varchar,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) END AS AmountPaid, tr.edemedpoints, tr.adjustedpoints, tr.expiredpoints

FROM tb_pointtransactions AS TA
JOIN tb_transactionsummaries tr on tr.transactionid=TA.transactionid
JOIN tb_memberships m ON TA.membershipid = m.membershipid
JOIN tb_users u ON u.UserID = m.UserID
LEFT JOIN tb_salesstatus s ON s.id = TA.salesstatus
INNER JOIN tb_saletransactions AS TB ON TA.TransactionID = TB.TransactionID AND TA.deletedby IS Null
WHERE membershipid = '1' ORDER BY year

i'm really appreciate your help, ManiD. I'm still a newbie in programming field. With your help, my learning path will be wonderful. Thank you very much.

how to store a word document in sql server

I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank youAsked and answered in .server -- please refrain from multiposting.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Ricardo Le Roux South Africa" <ricardolr@.mweb.co.za> wrote in message
news:eEbLTwOGFHA.3376@.TK2MSFTNGP12.phx.gbl...
> I Need help on how to store a word document in sql server.what datatype
and
> how to convert it to store it.
> Pleas Help
> Thank you
>

how to store a word document in sql server

I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank you
Asked and answered in .server -- please refrain from multiposting.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Ricardo Le Roux South Africa" <ricardolr@.mweb.co.za> wrote in message
news:eEbLTwOGFHA.3376@.TK2MSFTNGP12.phx.gbl...
> I Need help on how to store a word document in sql server.what datatype
and
> how to convert it to store it.
> Pleas Help
> Thank you
>

how to store a word document in sql

I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank you
See if this points you in the right direction:
http://vyaskn.tripod.com/programming_faq.htm#q5
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ricardo Le Roux South Africa" <ricardolr@.mweb.co.za> wrote in message
news:O6Ja0wOGFHA.208@.TK2MSFTNGP12.phx.gbl...
I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank you

how to store a word document in sql

I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank youSee if this points you in the right direction:
http://vyaskn.tripod.com/programming_faq.htm#q5
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ricardo Le Roux South Africa" <ricardolr@.mweb.co.za> wrote in message
news:O6Ja0wOGFHA.208@.TK2MSFTNGP12.phx.gbl...
I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank you

how to store a word document in sql

I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank you
Use the IMAGE data type to store the document. In general you would need to
store the document in memory and pass a reference to that memory as a
parameter to a stored procedure that deals with inserting or updating the
information. The implementation details on the application side would be
language specific.
Jim
"Ricardo Le Roux South Africa" <ricardolr@.mweb.co.za> wrote in message
news:OxWajwOGFHA.3908@.TK2MSFTNGP12.phx.gbl...
>I Need help on how to store a word document in sql server.what datatype
>and how to convert it to store it.
> Pleas Help
> Thank you
>

how to store a word document in sql

I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank youSee if this points you in the right direction:
http://vyaskn.tripod.com/programming_faq.htm#q5
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ricardo Le Roux South Africa" <ricardolr@.mweb.co.za> wrote in message
news:O6Ja0wOGFHA.208@.TK2MSFTNGP12.phx.gbl...
I Need help on how to store a word document in sql server.what datatype and
how to convert it to store it.
Pleas Help
Thank you