July 15, 2014 at 6:50 am
Thank you to everyone that read my response yesterday. Also Thank you to the guys that gave great comments. I used that code, slightly tweeked and everything worked fine.
Today I have a very similar situation, only today I am dealing with missing text data, not numeric data.
DECLARE @MissingTextData TABLE
(
RowID int
,UserID int
, EmailAddress varchar(20)
,StreetAddress varchar(20)
)
DECLARE @GoodTextData TABLE
(
RowID int
,UserID int
, EmailAddress varchar(20)
,StreetAddress varchar(20)
)
INSERT INTO @MissingTextData
SELECT 1,66886907,NULL, '123 Main Street' UNION ALL
SELECT 2,66886907,'MSlade@company.com', NULL
INSERT INTO @GoodTextData
SELECT 1,66886907,'MSlade@company.com', '123 Main Street'
SELECT * FROM @MissingTextData
SELECT * FROM @GoodTextData
I would like to fill in the NULL columns with data from the other row, and then select the one row that is filled with all data. I was able to use MAX() for a numeric value, but I am really stumped on the text data. Everything that I have tried is not working.
I greatly appreciate all your assistance, comments and suggestions. No, this is not homework. I am a little too old to be doing homework. I am working some code for my work.
Thanks
Andrew SQLDBA
July 15, 2014 at 8:17 am
Actually, MAX can be used in this case to eliminate NULL values, as well; since NULL represents unknown data, using MAX eliminates NULL, as the MAX is either a NULL if the column is only NULL, or actual text data if it's anything that isn't NULL:
SELECT 1,userid,MAX(emailaddress),MAX(StreetAddress)
FROM @MissingTextData
GROUP BY UserID
This will handle things in your test case, but again, if there's variance in the data (like a single UserID having multiple email or street addresses), this won't work. If that's the case, a more fluid solution can be developed if expanded sample data is provided 🙂
- 😀
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply