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 Test a Subscription

Hello,
I've currently got some email subscriptions setup to go out every 1 minute
(for testing purposes), but I'm not getting any emails and nowhere can I
find an error reported.
Is there a best practices for testing and troubleshooting subscriptions that
don't go out?
Any advice would be greatly appreciated!
Regards,
Benjamin PierceRegards,
I was able to figure out my problem -- it turns out that the ReportServer
Service was turned off (it's always the last place you look).
Just to share a little bit of knowledge with the RS users out there, here
are a few of the ways I personally troubleshoot subscriptions.
1) In the ReportServer database, execute the following query:
SELECT * FROM Event ORDER BY TimeEntered DESC
This will return the number of events in the queue. If items remain in this
queue for more than a minute or two, either the service is not on, something
is blocking in the database, or the service is hung.
2) Open SQL Enterprise Manager and check out the jobs. There should be a
job in your Enterprise Manager for each subscription. It might be useful to
look at the Event History, and check the error logs.
3) Of course, you'll also want to make sure you test the SMTP server that
you've got setup. A lot of the time the problem lies in the email setup
(possibly file sizes quotas, incorrectly configured email groups)...rather
than a Reporting Services thing.
Anyhow, I realize these tips are by no means advanced, but they've helped me
plenty of times and I hope they'll do the same for you.
Best Regards,
Benjamin Pierce
"Benjamin Pierce" <bpierce@.opentext.com> wrote in message
news:eonpvdcbEHA.796@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I've currently got some email subscriptions setup to go out every 1 minute
> (for testing purposes), but I'm not getting any emails and nowhere can I
> find an error reported.
> Is there a best practices for testing and troubleshooting subscriptions
that
> don't go out?
> Any advice would be greatly appreciated!
>
> Regards,
> Benjamin Pierce
>sql

How to tell if a job has hung?

using SQL7 and we have very occasionally had ascheduled nightly job
hang - it does not fail so we don't get an email it is just
"executing" - forever.

How could I test for this situation to generate an email if the job
runs longer than say 15 minutes?

Thanks

Billbill.. <b@.c.com> wrote in message news:<1tejqvkdp38mbv4rgncm373mu70af5306b@.4ax.com>...
> using SQL7 and we have very occasionally had ascheduled nightly job
> hang - it does not fail so we don't get an email it is just
> "executing" - forever.
> How could I test for this situation to generate an email if the job
> runs longer than say 15 minutes?
>
> Thanks
> Bill

Oen possibility is to query msdb..sysjobhistory and check the
run_status to see is it's still executing. You could also consider
finding the average run time for the job from the same table, and then
send an email only if the current execution time exceeds the average
by a certain threshold.

Simon|||Create another first step in the job to write the job name and start
datetime to a table. Add a final setp to write the completion.
Now have a schedule job to check for the start and completion - if the
completion does not turn up for 15 mins (can also check for the start)
then send an email.|||both good ideas

Thanks

Bill

How to store an email on SQL server 2000?

Is it possible to store an email in SQL server 2000? Do we need to define our own data type for that, if yes then how? or do we have to store it as an object, again how? I want to save email after sending it by my ASP .NET application and then retrieve it at a later stage......Anyone please help?

You could create a new email table and just log to it every time you send. That is what we do. Below is our table schema.

Email_Log
-----
ID int Not Null
From varchar(100) Not Null
To Text Not Null
CC Text
BCC Text
Subject varchar(250)
Body Text
CreatedDate DateTime

|||

Email_Log
-----
ID int Not Null
From varchar(100) Not Null
To Text Not Null
CC Text
BCC Text
Subject varchar(250)
Body Text
CreatedDate DateTime

We can only use this schema when the email body is plain text without pictures or formatting information. I would suggest storing the email body using binary files. Or we can store the email body on a file server, while keeping a link in the table which points to the location where the email is stored.

Hope my suggestion helps