Hello again,
I also want to truncate some data which is a floating point number. But
it is calculated as a result of two other columns.
for example lets say
column1: 2,4
column2: 4,1483
the result is: 9,95592
but I want to truncate the first 2 digits after the decimal point.
like 9,95
also what is the rounding formula and how to use?
Thanks in advance.You're really talking about display formatting -which is better done in the
client application.
If you must have SQL Server change the format of this computed value, try:
SELECT cast( (Column1 * Column2 ) AS decimal(10.2) )
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dot Net Daddy" <cagriandac@.gmail.com> wrote in message
news:1154637900.501096.298380@.m73g2000cwd.googlegroups.com...
> Hello again,
> I also want to truncate some data which is a floating point number. But
> it is calculated as a result of two other columns.
> for example lets say
> column1: 2,4
> column2: 4,1483
> the result is: 9,95592
> but I want to truncate the first 2 digits after the decimal point.
> like 9,95
> also what is the rounding formula and how to use?
>
> Thanks in advance.
>
Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts
Friday, March 30, 2012
How to Truncate
Hello again,
I also want to truncate some data which is a floating point number. But
it is calculated as a result of two other columns.
for example lets say
column1: 2,4
column2: 4,1483
the result is: 9,95592
but I want to truncate the first 2 digits after the decimal point.
like 9,95
also what is the rounding formula and how to use?
Thanks in advance.You're really talking about display formatting -which is better done in the
client application.
If you must have SQL Server change the format of this computed value, try:
SELECT cast( (Column1 * Column2 ) AS decimal(10.2) )
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dot Net Daddy" <cagriandac@.gmail.com> wrote in message
news:1154637900.501096.298380@.m73g2000cwd.googlegroups.com...
> Hello again,
> I also want to truncate some data which is a floating point number. But
> it is calculated as a result of two other columns.
> for example lets say
> column1: 2,4
> column2: 4,1483
> the result is: 9,95592
> but I want to truncate the first 2 digits after the decimal point.
> like 9,95
> also what is the rounding formula and how to use?
>
> Thanks in advance.
>
I also want to truncate some data which is a floating point number. But
it is calculated as a result of two other columns.
for example lets say
column1: 2,4
column2: 4,1483
the result is: 9,95592
but I want to truncate the first 2 digits after the decimal point.
like 9,95
also what is the rounding formula and how to use?
Thanks in advance.You're really talking about display formatting -which is better done in the
client application.
If you must have SQL Server change the format of this computed value, try:
SELECT cast( (Column1 * Column2 ) AS decimal(10.2) )
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dot Net Daddy" <cagriandac@.gmail.com> wrote in message
news:1154637900.501096.298380@.m73g2000cwd.googlegroups.com...
> Hello again,
> I also want to truncate some data which is a floating point number. But
> it is calculated as a result of two other columns.
> for example lets say
> column1: 2,4
> column2: 4,1483
> the result is: 9,95592
> but I want to truncate the first 2 digits after the decimal point.
> like 9,95
> also what is the rounding formula and how to use?
>
> Thanks in advance.
>
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
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
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 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
>
>
Monday, March 26, 2012
How to toggle visibility of a group of columns in RS
Hi,
I need to toggle visibility of group of columns in reporting services report, just like we can do for rows. I know that individual fields can be hidden, but i need to hide/show complete column.
Thanks in anticipation.
Saeed
Hi,
This is not possible in Reporting Services. When do you need to hide/unhide the column? Maybe I can help with finding an alternative solution. For example, you can set the Visibility property to the value of a boolean parameter.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||Hi
Thanks for your response.
I have came up with a work around. I have a summary coulmn based on few deatiled columns. Initialy when report is rendered, only summary column is visible, but has a + link associated with it to show detailed columns. Here is way to achieve it.
Get control name of heading of summary column. Then select all detail columns individualy and in visivility toggle item, type name of that heading summary column, and put visibility off. ( One caution here: Drop down list in visibility toggle item is empty. We will have to type summary heading control name here). Now, view report to test it.
Thanks
Saeed
|||asalamo alaykom saeed ahmad
you can do that by using models in reporting services
it means that when you using a model you can let the user select within the report builder to use columns dynamicly
and you can do this in a defferent way by using sophesticated tsql using if and then that execute query with a specific columns by passing some parameter for the stored proc
http://www.123writers.com
How to toggle visibility of a group of columns in RS
Hi,
I need to toggle visibility of group of columns in reporting services report, just like we can do for rows. I know that individual fields can be hidden, but i need to hide/show complete column.
Thanks in anticipation.
Saeed
Hi,
This is not possible in Reporting Services. When do you need to hide/unhide the column? Maybe I can help with finding an alternative solution. For example, you can set the Visibility property to the value of a boolean parameter.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||Hi
Thanks for your response.
I have came up with a work around. I have a summary coulmn based on few deatiled columns. Initialy when report is rendered, only summary column is visible, but has a + link associated with it to show detailed columns. Here is way to achieve it.
Get control name of heading of summary column. Then select all detail columns individualy and in visivility toggle item, type name of that heading summary column, and put visibility off. ( One caution here: Drop down list in visibility toggle item is empty. We will have to type summary heading control name here). Now, view report to test it.
Thanks
Saeed
|||asalamo alaykom saeed ahmad
you can do that by using models in reporting services
it means that when you using a model you can let the user select within the report builder to use columns dynamicly
and you can do this in a defferent way by using sophesticated tsql using if and then that execute query with a specific columns by passing some parameter for the stored proc
http://www.123writers.com
Friday, March 9, 2012
how to subquery with 2 or multiple columns
This is a small problem .. hope I could find quick answer
I am trying to write a query which uses a subquery in its where
condition. there are 2 columns (both PK) for which checking is
required.
I can run this query in DB2 with no problems. what is the correct
approach to do that in SQL Server 2005 ?
SELECT EMP_ID,DEPT_ID
FROM
STAGE_RMRS_PPLSFT
WHERE
(EMP_ID,DEPT_ID)
NOT IN
(SELECT MANAGER_ID,MGR_DEPT_ID
FROM
STAGE_RMRS_PPLSFT
)
Note : I did managed to do the same in SQL with combining the 2
columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
as index scan doesn't apply here.
pls suggest me a better solution
"Ashish Prasad" <ashishbitm@.gmail.com> wrote in message
news:97e9525a-73c6-4b5d-a39e-e0d872779f48@.d70g2000hsb.googlegroups.com...
> This is a small problem .. hope I could find quick answer
> I am trying to write a query which uses a subquery in its where
> condition. there are 2 columns (both PK) for which checking is
> required.
> I can run this query in DB2 with no problems. what is the correct
> approach to do that in SQL Server 2005 ?
> SELECT EMP_ID,DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> WHERE
> (EMP_ID,DEPT_ID)
> NOT IN
> (SELECT MANAGER_ID,MGR_DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> )
> Note : I did managed to do the same in SQL with combining the 2
> columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
> as index scan doesn't apply here.
> pls suggest me a better solution
Try one of the following. There is a difference however. Your query would
return no rows at all if either of MANAGER_ID or MGR_DEPT_ID contain any
nulls. Very often that is not a desirable result so the two alternatives
I've given here might be suitable.
SELECT EMP_ID,DEPT_ID
FROM STAGE_RMRS_PPLSFT
EXCEPT
SELECT MANAGER_ID, MGR_DEPT_ID
FROM STAGE_RMRS_PPLSFT;
SELECT EMP_ID,DEPT_ID
FROM STAGE_RMRS_PPLSFT S
WHERE NOT EXISTS
(SELECT MANAGER_ID,MGR_DEPT_ID
FROM STAGE_RMRS_PPLSFT
WHERE MANAGER_ID = S.EMP_ID
AND MGR_DEPT_ID = S.DEPT_ID);
Going by the column names alone I would guess this table is not well
normalized (MANAGER_ID->MGR_DEPT_ID?), which is something you should
probably consider fixing.
HTH
David Portas
|||will use the correlated query with EXIST clause ..
Thanks a lot for your Help..
I am trying to write a query which uses a subquery in its where
condition. there are 2 columns (both PK) for which checking is
required.
I can run this query in DB2 with no problems. what is the correct
approach to do that in SQL Server 2005 ?
SELECT EMP_ID,DEPT_ID
FROM
STAGE_RMRS_PPLSFT
WHERE
(EMP_ID,DEPT_ID)
NOT IN
(SELECT MANAGER_ID,MGR_DEPT_ID
FROM
STAGE_RMRS_PPLSFT
)
Note : I did managed to do the same in SQL with combining the 2
columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
as index scan doesn't apply here.
pls suggest me a better solution
"Ashish Prasad" <ashishbitm@.gmail.com> wrote in message
news:97e9525a-73c6-4b5d-a39e-e0d872779f48@.d70g2000hsb.googlegroups.com...
> This is a small problem .. hope I could find quick answer
> I am trying to write a query which uses a subquery in its where
> condition. there are 2 columns (both PK) for which checking is
> required.
> I can run this query in DB2 with no problems. what is the correct
> approach to do that in SQL Server 2005 ?
> SELECT EMP_ID,DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> WHERE
> (EMP_ID,DEPT_ID)
> NOT IN
> (SELECT MANAGER_ID,MGR_DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> )
> Note : I did managed to do the same in SQL with combining the 2
> columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
> as index scan doesn't apply here.
> pls suggest me a better solution
Try one of the following. There is a difference however. Your query would
return no rows at all if either of MANAGER_ID or MGR_DEPT_ID contain any
nulls. Very often that is not a desirable result so the two alternatives
I've given here might be suitable.
SELECT EMP_ID,DEPT_ID
FROM STAGE_RMRS_PPLSFT
EXCEPT
SELECT MANAGER_ID, MGR_DEPT_ID
FROM STAGE_RMRS_PPLSFT;
SELECT EMP_ID,DEPT_ID
FROM STAGE_RMRS_PPLSFT S
WHERE NOT EXISTS
(SELECT MANAGER_ID,MGR_DEPT_ID
FROM STAGE_RMRS_PPLSFT
WHERE MANAGER_ID = S.EMP_ID
AND MGR_DEPT_ID = S.DEPT_ID);
Going by the column names alone I would guess this table is not well
normalized (MANAGER_ID->MGR_DEPT_ID?), which is something you should
probably consider fixing.
HTH
David Portas
|||will use the correlated query with EXIST clause ..
Thanks a lot for your Help..
how to subquery with 2 or multiple columns
This is a small problem .. hope I could find quick answer :)
I am trying to write a query which uses a subquery in its where
condition. there are 2 columns (both PK) for which checking is
required.
I can run this query in DB2 with no problems. what is the correct
approach to do that in SQL Server 2005 ?
SELECT EMP_ID,DEPT_ID
FROM
STAGE_RMRS_PPLSFT
WHERE
(EMP_ID,DEPT_ID)
NOT IN
(SELECT MANAGER_ID,MGR_DEPT_ID
FROM
STAGE_RMRS_PPLSFT
)
Note : I did managed to do the same in SQL with combining the 2
columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
as index scan doesn't apply here.
pls suggest me a better solutionAs you've noticed, SQL Server doesn't allow multiple columns in IN (row-valued constructors). I
suggest you use EXISTS instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ashish Prasad" <ashishbitm@.gmail.com> wrote in message
news:97e9525a-73c6-4b5d-a39e-e0d872779f48@.d70g2000hsb.googlegroups.com...
> This is a small problem .. hope I could find quick answer :)
> I am trying to write a query which uses a subquery in its where
> condition. there are 2 columns (both PK) for which checking is
> required.
> I can run this query in DB2 with no problems. what is the correct
> approach to do that in SQL Server 2005 ?
> SELECT EMP_ID,DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> WHERE
> (EMP_ID,DEPT_ID)
> NOT IN
> (SELECT MANAGER_ID,MGR_DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> )
> Note : I did managed to do the same in SQL with combining the 2
> columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
> as index scan doesn't apply here.
> pls suggest me a better solution|||"Ashish Prasad" <ashishbitm@.gmail.com> wrote in message
news:97e9525a-73c6-4b5d-a39e-e0d872779f48@.d70g2000hsb.googlegroups.com...
> This is a small problem .. hope I could find quick answer :)
> I am trying to write a query which uses a subquery in its where
> condition. there are 2 columns (both PK) for which checking is
> required.
> I can run this query in DB2 with no problems. what is the correct
> approach to do that in SQL Server 2005 ?
> SELECT EMP_ID,DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> WHERE
> (EMP_ID,DEPT_ID)
> NOT IN
> (SELECT MANAGER_ID,MGR_DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> )
> Note : I did managed to do the same in SQL with combining the 2
> columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
> as index scan doesn't apply here.
> pls suggest me a better solution
Try one of the following. There is a difference however. Your query would
return no rows at all if either of MANAGER_ID or MGR_DEPT_ID contain any
nulls. Very often that is not a desirable result so the two alternatives
I've given here might be suitable.
SELECT EMP_ID,DEPT_ID
FROM STAGE_RMRS_PPLSFT
EXCEPT
SELECT MANAGER_ID, MGR_DEPT_ID
FROM STAGE_RMRS_PPLSFT;
SELECT EMP_ID,DEPT_ID
FROM STAGE_RMRS_PPLSFT S
WHERE NOT EXISTS
(SELECT MANAGER_ID,MGR_DEPT_ID
FROM STAGE_RMRS_PPLSFT
WHERE MANAGER_ID = S.EMP_ID
AND MGR_DEPT_ID = S.DEPT_ID);
Going by the column names alone I would guess this table is not well
normalized (MANAGER_ID->MGR_DEPT_ID?), which is something you should
probably consider fixing.
HTH
--
David Portas|||will use the correlated query with EXIST clause ..
Thanks a lot for your Help.. :)
I am trying to write a query which uses a subquery in its where
condition. there are 2 columns (both PK) for which checking is
required.
I can run this query in DB2 with no problems. what is the correct
approach to do that in SQL Server 2005 ?
SELECT EMP_ID,DEPT_ID
FROM
STAGE_RMRS_PPLSFT
WHERE
(EMP_ID,DEPT_ID)
NOT IN
(SELECT MANAGER_ID,MGR_DEPT_ID
FROM
STAGE_RMRS_PPLSFT
)
Note : I did managed to do the same in SQL with combining the 2
columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
as index scan doesn't apply here.
pls suggest me a better solutionAs you've noticed, SQL Server doesn't allow multiple columns in IN (row-valued constructors). I
suggest you use EXISTS instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ashish Prasad" <ashishbitm@.gmail.com> wrote in message
news:97e9525a-73c6-4b5d-a39e-e0d872779f48@.d70g2000hsb.googlegroups.com...
> This is a small problem .. hope I could find quick answer :)
> I am trying to write a query which uses a subquery in its where
> condition. there are 2 columns (both PK) for which checking is
> required.
> I can run this query in DB2 with no problems. what is the correct
> approach to do that in SQL Server 2005 ?
> SELECT EMP_ID,DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> WHERE
> (EMP_ID,DEPT_ID)
> NOT IN
> (SELECT MANAGER_ID,MGR_DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> )
> Note : I did managed to do the same in SQL with combining the 2
> columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
> as index scan doesn't apply here.
> pls suggest me a better solution|||"Ashish Prasad" <ashishbitm@.gmail.com> wrote in message
news:97e9525a-73c6-4b5d-a39e-e0d872779f48@.d70g2000hsb.googlegroups.com...
> This is a small problem .. hope I could find quick answer :)
> I am trying to write a query which uses a subquery in its where
> condition. there are 2 columns (both PK) for which checking is
> required.
> I can run this query in DB2 with no problems. what is the correct
> approach to do that in SQL Server 2005 ?
> SELECT EMP_ID,DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> WHERE
> (EMP_ID,DEPT_ID)
> NOT IN
> (SELECT MANAGER_ID,MGR_DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> )
> Note : I did managed to do the same in SQL with combining the 2
> columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
> as index scan doesn't apply here.
> pls suggest me a better solution
Try one of the following. There is a difference however. Your query would
return no rows at all if either of MANAGER_ID or MGR_DEPT_ID contain any
nulls. Very often that is not a desirable result so the two alternatives
I've given here might be suitable.
SELECT EMP_ID,DEPT_ID
FROM STAGE_RMRS_PPLSFT
EXCEPT
SELECT MANAGER_ID, MGR_DEPT_ID
FROM STAGE_RMRS_PPLSFT;
SELECT EMP_ID,DEPT_ID
FROM STAGE_RMRS_PPLSFT S
WHERE NOT EXISTS
(SELECT MANAGER_ID,MGR_DEPT_ID
FROM STAGE_RMRS_PPLSFT
WHERE MANAGER_ID = S.EMP_ID
AND MGR_DEPT_ID = S.DEPT_ID);
Going by the column names alone I would guess this table is not well
normalized (MANAGER_ID->MGR_DEPT_ID?), which is something you should
probably consider fixing.
HTH
--
David Portas|||will use the correlated query with EXIST clause ..
Thanks a lot for your Help.. :)
How to structure dimension....?
I have a standard employee-manager tree that I want to use in a dimension. the underlying table has [id] and [parent-id] columns that I've used to create a parent-child relationship in the dimension (via the Usage = /Parent/ field)
Because there are so many top-level members in the tree, I want to create a level above it consisting of the starting letter for manager's last names. This would allow users to select B and see Badden, Browne, Bronson, etc. after which the user could click on Browne and see his employees: Alder, Carson, Domino, etc.
what is the best way of implementing this? I've tried a number of ways without success.including creating a hierarchy consisting of the [Group] (the column witht he first letter of the last name) in the first level and the [parent-id] (Usage = /Parent/, where the the [id]'s NameColumn = /Last Name/) but I can't build it as it pukes with the error:
"Error 1 Dimension 'OrgChart' > Hierarchy 'Hierarchy' > Level 'ParentID' : The source attribute of the level is marked as 'Parent'. 0 0 "
can anyone help?You cannot mix and match other attributes in with a parent child hierarchy.
The only way I know of to implement this is to add the records for the A-Z members to your dimension table and link them up appropriately in there. If you did not want to mix these records in with "real" employees you could create a separate table and link it in with a union query in a view or a named query in the DSV.
Subscribe to:
Posts (Atom)