July 6, 2006 at 9:53 am
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.
July 6, 2006 at 10:02 am
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.
July 6, 2006 at 11:01 am
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.
July 6, 2006 at 11:17 am
Although I agree with Julie..., GREAT HACK, Ryan!
I wasn't born stupid - I had to study.
July 6, 2006 at 11:28 am
Agreed, Farrell. I forget about using MAX on non-numeric data...
July 6, 2006 at 11:35 am
My specialty
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 6, 2006 at 12:37 pm
I dunno - they were trying to call me Bulldozer there for a while...
I wasn't born stupid - I had to study.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply