strip out NULL from recordset and merge

Question

strip out NULL from recordset and merge

hren

Ten Centuries

Points: 1220
More actions
July 6, 2006 at 9:53 am

#153074

I know it's unclear from Subject. But here is the deal. I have a procedure, very long procedure that get's me result I want, but I was only able to produce recordset in format as such:
UserID Company LastName FirstName Email
149NULLNULLNULLjoc@mycon.com
149NULLNULLJackNULL
149NULLSmithNULLNULL
149My BussinesNULLNULLNULL
613NULLNULLNULLmy@mail.com
613NULLNULLTestFirstNULL
613NULLTestLastNULLNULL
613MyCompanyNULLNULLNULL
617NULLNULLNULLDummyTest
617NULLNULLDummyTestNULL
617NULLDummyTestNULLNULL
617DummyTestNULLNULLNULL
As you can see it doesn't want to put all records together properly under each user and does what's above instead, I really tried to do normal way, but due to extrem dynamic queries and temp tables that's as far as I could get.
Now my question is, how can I strip all NULLs out? To Have result like:
149My BussinesSmithJackjoc@mycon.com
613MyCompanyTestLast TestFirstmy@mail.com
617DummyTestDummyTestDummyTestDummyTest
The ugly result I get is from simple select as
exec('SELECT DISTINCT UserID, ' + @SelectString + ' FROM ##TT')
So I can modify it or insert into another temp table, but I need to get rid off those NULLS. Please HELP! This is very urgent.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Julie Hargraves SSCarpal Tunnel Points: 4457 More actions · Answer 1

It looks like the problem is in the Insert on your temporary table. You are probably inserting multiple rows for each UserId instead of updating an existing row. You might want to look into this instead of altering your SELECT statement.

RyanRandall SSChampion Points: 13623 More actions · Answer 2

As a nasty hack, you can use the approach below, but you're much better off sorting out the problem at source (i.e. where you create that ugly result set).

--data

declare @t table (UserID int, Company varchar(20), LastName varchar(20), FirstName varchar(20), Email varchar(20))

insert @t

select 149, NULL, NULL, NULL, 'joc@mycon.com'

union all select 149, NULL, NULL, 'Jack', NULL

union all select 149, NULL, 'Smith', NULL, NULL

union all select 149, 'My Bussines', NULL, NULL, NULL

union all select 613, NULL, NULL, NULL, 'my@mail.com'

union all select 613, NULL, NULL, 'TestFirst', NULL

union all select 613, NULL, 'TestLast', NULL, NULL

union all select 613, 'MyCompany', NULL, NULL, NULL

union all select 617, NULL, NULL, NULL, 'DummyTest'

union all select 617, NULL, NULL, 'DummyTest', NULL

union all select 617, NULL, 'DummyTest', NULL, NULL

union all select 617, 'DummyTest', NULL, NULL, NULL

--calculation

select UserId, max(Company) as Company, max(LastName) as LastName,

max(FirstName) as FirstName, max(Email) as Email

from @t group by UserId

/*results

UserId Company LastName FirstName Email

----------- -------------------- -------------------- -------------------- --------------------

149 My Bussines Smith Jack joc@mycon.com

613 MyCompany TestLast TestFirst my@mail.com

617 DummyTest DummyTest DummyTest DummyTest

*/

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

Although I agree with Julie..., GREAT HACK, Ryan!

I wasn't born stupid - I had to study.

Pam Brisjar SSChampion Points: 12094 More actions · Answer 4

Agreed, Farrell. I forget about using MAX on non-numeric data...

RyanRandall SSChampion Points: 13623 More actions · Answer 5

My specialty

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Farrell Keough SSCoach Points: 17414 More actions · Answer 6

I dunno - they were trying to call me Bulldozer there for a while...

I wasn't born stupid - I had to study.