Removing NULL in CASE ELSE

How to compress the output into single line
if got the below output using case..else
9155 michael NULL NULL NULL
9155 NULL NULL narain NULL
9155 NULL NULL NULL karthik
9155 NULL shumaker NULL NULL
Thanks
krishHi Krish,
Could you please be so kind give us more specific details that are you tryin
g?
Thanks in advance and regards,
"krish" wrote:

> How to compress the output into single line
> if got the below output using case..else
> 9155 michael NULL NULL NULL
> 9155 NULL NULL narain NULL
> 9155 NULL NULL NULL karthik
> 9155 NULL shumaker NULL NULL
> Thanks
> krish|||Try grouping by the first column and using min or max aggregate function.
Example:
select col1, min(case when ... end), min(case when ... end)
from table1
group by col1
AMB
"krish" wrote:

> How to compress the output into single line
> if got the below output using case..else
> 9155 michael NULL NULL NULL
> 9155 NULL NULL narain NULL
> 9155 NULL NULL NULL karthik
> 9155 NULL shumaker NULL NULL
> Thanks
> krish|||SELECT a, MAX(b), MAX(c), MAX(d), MAX(e)
FROM
(
SELECT 9155 as a, 'michael' as b, NULL AS c, NULL AS d, NULL AS e
UNION
SELECT 9155, NULL, NULL, 'narain', NULL
UNION
SELECT 9155, NULL, NULL, NULL, 'karthik'
UNION
SELECT 9155, NULL, 'shumaker', NULL, NULL
) AS sub
GROUP BY a
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"krish" <krish@.discussions.microsoft.com> wrote in message
news:A859613F-79B8-4BF2-840E-CA33BB9F42B2@.microsoft.com...
> How to compress the output into single line
> if got the below output using case..else
> 9155 michael NULL NULL NULL
> 9155 NULL NULL narain NULL
> 9155 NULL NULL NULL karthik
> 9155 NULL shumaker NULL NULL
> Thanks
> krish

Removing case sensitivity

Is there a way to remove Case Sensitivity from a SQL 2000 server? I can't
imagine why anyone would set the server to this but one of my vendors did and
I hate needing to know exactly where to put my caps...
TIA,
Joe
This should help :-
http://support.microsoft.com/default...b;en-us;325335
HTH. Ryan
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:068ACE9B-3F12-4ED0-926A-8826B059CA3D@.microsoft.com...
> Is there a way to remove Case Sensitivity from a SQL 2000 server? I can't
> imagine why anyone would set the server to this but one of my vendors did
> and
> I hate needing to know exactly where to put my caps...
> TIA,
> Joe
>
|||"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:068ACE9B-3F12-4ED0-926A-8826B059CA3D@.microsoft.com...
> Is there a way to remove Case Sensitivity from a SQL 2000 server? I can't
> imagine why anyone would set the server to this but one of my vendors did
> and
> I hate needing to know exactly where to put my caps...
> TIA,
> Joe
>
Case sensitive db's can be faster. ;-)
You don't have to figure out whether or not Apple = APPLE = ApPle etc. You
can do a simple binary compare.
Rick Sawtell
MCT, MCSD, MCDBA
|||Thank you I will check this out.
From a quick look it seems like I have to rebuild the SQL server for this?
Thanks Again,
Joe
"Ryan" wrote:

> This should help :-
> http://support.microsoft.com/default...b;en-us;325335
> --
> HTH. Ryan
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:068ACE9B-3F12-4ED0-926A-8826B059CA3D@.microsoft.com...
>
>
|||Yes you are correct, but when I have a query "select * from apple_tbl" I get
an error saying the object does not exist becuase it is spelled apple_Tbl.
How do you get around every query and SP you write?
Thanks for the response,
Joe
"Rick Sawtell" wrote:

> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:068ACE9B-3F12-4ED0-926A-8826B059CA3D@.microsoft.com...
> Case sensitive db's can be faster. ;-)
> You don't have to figure out whether or not Apple = APPLE = ApPle etc. You
> can do a simple binary compare.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||Afraid so..
HTH. Ryan
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:819D50DF-DF8A-49CE-B6D2-17E822F8A742@.microsoft.com...[vbcol=seagreen]
> Thank you I will check this out.
> From a quick look it seems like I have to rebuild the SQL server for this?
> Thanks Again,
> Joe
>
> "Ryan" wrote:
|||jaylou wrote:
> Yes you are correct, but when I have a query "select * from
> apple_tbl" I get an error saying the object does not exist becuase it
> is spelled apple_Tbl.
> How do you get around every query and SP you write?
>
You do not have to reinstall SQL Server to change a database collation.
You can create a new database with the correct collation and then
migrate the data. System tables in master will still be case sensitive.
You can also change the database collation directly and then copy the
tables in the database (changing the collation does not change the
collation of existing objects)
-- for example
alter database MyDB
COLLATE SQL_Latin1_General_CP1_CI_AS
You could also rename your objects in the existing database to use
lower-case to avoid these issues. While the standard has moved away from
case-sensitive collations, I do think they offer some advantages since
they keep object names capitalized the same in the database.
David Gugick - SQL Server MVP
Quest Software
|||and some of us hate when applications are written that don't use the correct
case and barf on case-sensitive servers ;)
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:2FD17568-400F-40DD-953A-D0181683761E@.microsoft.com...[vbcol=seagreen]
> Yes you are correct, but when I have a query "select * from apple_tbl" I
> get
> an error saying the object does not exist becuase it is spelled apple_Tbl.
> How do you get around every query and SP you write?
> Thanks for the response,
> Joe
> "Rick Sawtell" wrote:

Removing case sensitivity

Is there a way to remove Case Sensitivity from a SQL 2000 server? I can't
imagine why anyone would set the server to this but one of my vendors did and
I hate needing to know exactly where to put my caps...
TIA,
JoeThis should help :-
http://support.microsoft.com/default.aspx?scid=kb;en-us;325335
--
HTH. Ryan
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:068ACE9B-3F12-4ED0-926A-8826B059CA3D@.microsoft.com...
> Is there a way to remove Case Sensitivity from a SQL 2000 server? I can't
> imagine why anyone would set the server to this but one of my vendors did
> and
> I hate needing to know exactly where to put my caps...
> TIA,
> Joe
>|||"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:068ACE9B-3F12-4ED0-926A-8826B059CA3D@.microsoft.com...
> Is there a way to remove Case Sensitivity from a SQL 2000 server? I can't
> imagine why anyone would set the server to this but one of my vendors did
> and
> I hate needing to know exactly where to put my caps...
> TIA,
> Joe
>
Case sensitive db's can be faster. ;-)
You don't have to figure out whether or not Apple = APPLE = ApPle etc. You
can do a simple binary compare.
Rick Sawtell
MCT, MCSD, MCDBA|||Thank you I will check this out.
From a quick look it seems like I have to rebuild the SQL server for this?
Thanks Again,
Joe
"Ryan" wrote:
> This should help :-
> http://support.microsoft.com/default.aspx?scid=kb;en-us;325335
> --
> HTH. Ryan
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:068ACE9B-3F12-4ED0-926A-8826B059CA3D@.microsoft.com...
> > Is there a way to remove Case Sensitivity from a SQL 2000 server? I can't
> > imagine why anyone would set the server to this but one of my vendors did
> > and
> > I hate needing to know exactly where to put my caps...
> >
> > TIA,
> > Joe
> >
>
>|||Yes you are correct, but when I have a query "select * from apple_tbl" I get
an error saying the object does not exist becuase it is spelled apple_Tbl.
How do you get around every query and SP you write?
Thanks for the response,
Joe
"Rick Sawtell" wrote:
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:068ACE9B-3F12-4ED0-926A-8826B059CA3D@.microsoft.com...
> > Is there a way to remove Case Sensitivity from a SQL 2000 server? I can't
> > imagine why anyone would set the server to this but one of my vendors did
> > and
> > I hate needing to know exactly where to put my caps...
> >
> > TIA,
> > Joe
> >
> Case sensitive db's can be faster. ;-)
> You don't have to figure out whether or not Apple = APPLE = ApPle etc. You
> can do a simple binary compare.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Afraid so..
--
HTH. Ryan
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:819D50DF-DF8A-49CE-B6D2-17E822F8A742@.microsoft.com...
> Thank you I will check this out.
> From a quick look it seems like I have to rebuild the SQL server for this?
> Thanks Again,
> Joe
>
> "Ryan" wrote:
>> This should help :-
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;325335
>> --
>> HTH. Ryan
>> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
>> news:068ACE9B-3F12-4ED0-926A-8826B059CA3D@.microsoft.com...
>> > Is there a way to remove Case Sensitivity from a SQL 2000 server? I
>> > can't
>> > imagine why anyone would set the server to this but one of my vendors
>> > did
>> > and
>> > I hate needing to know exactly where to put my caps...
>> >
>> > TIA,
>> > Joe
>> >
>>|||jaylou wrote:
> Yes you are correct, but when I have a query "select * from
> apple_tbl" I get an error saying the object does not exist becuase it
> is spelled apple_Tbl.
> How do you get around every query and SP you write?
>
You do not have to reinstall SQL Server to change a database collation.
You can create a new database with the correct collation and then
migrate the data. System tables in master will still be case sensitive.
You can also change the database collation directly and then copy the
tables in the database (changing the collation does not change the
collation of existing objects)
-- for example
alter database MyDB
COLLATE SQL_Latin1_General_CP1_CI_AS
You could also rename your objects in the existing database to use
lower-case to avoid these issues. While the standard has moved away from
case-sensitive collations, I do think they offer some advantages since
they keep object names capitalized the same in the database.
David Gugick - SQL Server MVP
Quest Software|||and some of us hate when applications are written that don't use the correct
case and barf on case-sensitive servers ;)
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:2FD17568-400F-40DD-953A-D0181683761E@.microsoft.com...
> Yes you are correct, but when I have a query "select * from apple_tbl" I
> get
> an error saying the object does not exist becuase it is spelled apple_Tbl.
> How do you get around every query and SP you write?
> Thanks for the response,
> Joe
> "Rick Sawtell" wrote:
>> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
>> news:068ACE9B-3F12-4ED0-926A-8826B059CA3D@.microsoft.com...
>> > Is there a way to remove Case Sensitivity from a SQL 2000 server? I
>> > can't
>> > imagine why anyone would set the server to this but one of my vendors
>> > did
>> > and
>> > I hate needing to know exactly where to put my caps...
>> >
>> > TIA,
>> > Joe
>> >
>> Case sensitive db's can be faster. ;-)
>> You don't have to figure out whether or not Apple = APPLE = ApPle etc.
>> You
>> can do a simple binary compare.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>

Removing case sensitivity

Is there a way to remove Case Sensitivity from a SQL 2000 server? I can't
imagine why anyone would set the server to this but one of my vendors did an
d
I hate needing to know exactly where to put my caps...
TIA,
JoeThis should help :-
http://support.microsoft.com/defaul...kb;en-us;325335
HTH. Ryan
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:068ACE9B-3F12-4ED0-926A-8826B059CA3D@.microsoft.com...
> Is there a way to remove Case Sensitivity from a SQL 2000 server? I can't
> imagine why anyone would set the server to this but one of my vendors did
> and
> I hate needing to know exactly where to put my caps...
> TIA,
> Joe
>|||"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:068ACE9B-3F12-4ED0-926A-8826B059CA3D@.microsoft.com...
> Is there a way to remove Case Sensitivity from a SQL 2000 server? I can't
> imagine why anyone would set the server to this but one of my vendors did
> and
> I hate needing to know exactly where to put my caps...
> TIA,
> Joe
>
Case sensitive db's can be faster. ;-)
You don't have to figure out whether or not Apple = APPLE = ApPle etc. You
can do a simple binary compare.
Rick Sawtell
MCT, MCSD, MCDBA|||Thank you I will check this out.
From a quick look it seems like I have to rebuild the SQL server for this?
Thanks Again,
Joe
"Ryan" wrote:

> This should help :-
> http://support.microsoft.com/defaul...kb;en-us;325335
> --
> HTH. Ryan
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:068ACE9B-3F12-4ED0-926A-8826B059CA3D@.microsoft.com...
>
>|||Yes you are correct, but when I have a query "select * from apple_tbl" I get
an error saying the object does not exist becuase it is spelled apple_Tbl.
How do you get around every query and SP you write?
Thanks for the response,
Joe
"Rick Sawtell" wrote:

> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:068ACE9B-3F12-4ED0-926A-8826B059CA3D@.microsoft.com...
> Case sensitive db's can be faster. ;-)
> You don't have to figure out whether or not Apple = APPLE = ApPle etc. Y
ou
> can do a simple binary compare.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Afraid so..
HTH. Ryan
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:819D50DF-DF8A-49CE-B6D2-17E822F8A742@.microsoft.com...[vbcol=seagreen]
> Thank you I will check this out.
> From a quick look it seems like I have to rebuild the SQL server for this?
> Thanks Again,
> Joe
>
> "Ryan" wrote:
>|||jaylou wrote:
> Yes you are correct, but when I have a query "select * from
> apple_tbl" I get an error saying the object does not exist becuase it
> is spelled apple_Tbl.
> How do you get around every query and SP you write?
>
You do not have to reinstall SQL Server to change a database collation.
You can create a new database with the correct collation and then
migrate the data. System tables in master will still be case sensitive.
You can also change the database collation directly and then copy the
tables in the database (changing the collation does not change the
collation of existing objects)
-- for example
alter database MyDB
COLLATE SQL_Latin1_General_CP1_CI_AS
You could also rename your objects in the existing database to use
lower-case to avoid these issues. While the standard has moved away from
case-sensitive collations, I do think they offer some advantages since
they keep object names capitalized the same in the database.
David Gugick - SQL Server MVP
Quest Software|||and some of us hate when applications are written that don't use the correct
case and barf on case-sensitive servers ;)
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:2FD17568-400F-40DD-953A-D0181683761E@.microsoft.com...[vbcol=seagreen]
> Yes you are correct, but when I have a query "select * from apple_tbl" I
> get
> an error saying the object does not exist becuase it is spelled apple_Tbl.
> How do you get around every query and SP you write?
> Thanks for the response,
> Joe
> "Rick Sawtell" wrote:
>