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 headers from sql output

I'm writing a query which creates a script that I then want to be able to run, but can't get rid of the headers.

eg. my query is similar to this:
USE master
go
set nocount on
go
select 'restore database @.dbname = N' + "'" + db_name() + "'" + ','
go
select '@.filename' +convert(varchar(5),fileid)+ ' = N'+convert(varchar(50),filename)+',' from sysfiles
go

The output is similar this:
------------------------------------------------
restore database @.dbname = N'master',

---------------------
@.filename1 = Nd:\sysdata\SQL2000\MSSQL${instancename}\data\mast er.mdf,
@.filename2 = Nd:\sysdata\SQL2000\MSSQL${instancename}\data\mast log.ld,

I want to get rid of all the '---' and lines between the output.

Can anyone help please?Hello,

which database do you use ? Looks like MSSQL ?

Regards

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||I'm writing the script for SQL2000 -

removing blank spaces from output file

Is there a way to remove blank space padding from around
my result set sent to a text file using osql. I can only
seem to find a column_width setting. I am using the below
and am after a csv file without blank padding.
osql -S(local) -Uuser -Ppassword -s, -h-1 -w254 -
Q"DMSWEB.dbo.rf_test 200035" -oc:\TestReport3.txt
Thanks in advance.
Amelia
Hi,
Consider BCP with QUERYOUT option to do this.
Sample
bcp "exec DMSWEB.dbo.rf_test 200035" queryout
c:\TestReport3.txt -c -S(local) -Uuser -Ppassword
Thanks
Hari
MCDBA
"Amelai" <anonymous@.discussions.microsoft.com> wrote in message
news:2352201c45e43$427d3c60$a501280a@.phx.gbl...
> Is there a way to remove blank space padding from around
> my result set sent to a text file using osql. I can only
> seem to find a column_width setting. I am using the below
> and am after a csv file without blank padding.
> osql -S(local) -Uuser -Ppassword -s, -h-1 -w254 -
> Q"DMSWEB.dbo.rf_test 200035" -oc:\TestReport3.txt
> Thanks in advance.
> Amelia
|||Thanks heaps for your help Hari. Much appreciated.
:0)
Amelia

>--Original Message--
>Hi,
>Consider BCP with QUERYOUT option to do this.
>Sample
>bcp "exec DMSWEB.dbo.rf_test 200035" queryout
>c:\TestReport3.txt -c -S(local) -Uuser -Ppassword
>--
>Thanks
>Hari
>MCDBA
>"Amelai" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2352201c45e43$427d3c60$a501280a@.phx.gbl...
below
>
>.
>