April 10, 2012 at 9:44 am
Yes Mike you are correct. The version that ColdCoffee posted just after mine is almost the same thing as the one I posted. It has the same issue too.
DECLARE @Example
TABLE (
row_id INTEGER IDENTITY PRIMARY KEY,
address1 VARCHAR(50) NULL,
address2 VARCHAR(50) NULL,
address3 VARCHAR(50) NULL
);
INSERT @Example
(address1, address2, address3)
VALUES ('House 1', 'A Road', NULL);
INSERT @Example
(address1, address2, address3)
VALUES ('House 2', NULL, 'Another Road');
INSERT @Example
(address1, address2, address3)
VALUES (NULL, NULL, 'Another Road');
INSERT @Example
(address1, address2, address3)
VALUES (NULL, NULL, NULL);
INSERT @Example
(address1, address2, address3)
VALUES (NULL, 'Add1', NULL);
INSERT @Example
(address1, address2, address3)
VALUES (NULL, null, 'Add1');
SELECT
row_id
,Newaddress1 = COALESCE(address1, address2, address3)
,Newaddress2 = CASE WHEN address1 IS NULL THEN address3
ELSE COALESCE(address2, address3)
END
,Newaddress3 = CASE WHEN address1 + address2 IS NULL THEN NULL
ELSE address3
END
FROM @Example
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 10, 2012 at 9:48 am
Just need to add another case statement to make it work...not that this would be the preferred way given so many other solutions though.
create table #Address
(
id int,
addr1 varchar(25),
addr2 varchar(25),
addr3 varchar(25)
)
insert #Address
select 1, '123', NULL, 'Main St.'
union all select 2, NULL, NULL, NULL
union all select 3, NULL, '345', 'Elm St.'
union all select 4, '678', 'Apt 4', 'Winding Rd.'
union all select 5, '910', 'Rodeo Dr.', NULL
union all select 6, '121', NULL, NULL
union all select 7, null, null, 'some where'
select * from #Address
update #Address set
addr1 = COALESCE(addr1, addr2, addr3),
addr2 = case
when addr1 IS null
then
case
when addr2 is null then null
else coalesce(addr2, addr3)
end
end,
addr3 = case
when addr1 IS null then null
when addr2 IS null then null
else addr3
end
select * from #Address
DROP TABLE #Address
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 10, 2012 at 11:30 am
If the ultimate goal is concatenation, then there's no reason to shift columns left. Just do the concatenation as normal.
with mySampleData (id,addr1,addr2,addr3)
AS
(
SELECT'1','123',NULL,'Main St.' UNION ALL
SELECT'2',NULL,NULL,NULL UNION ALL
SELECT'3',NULL,'345','Elm St.' UNION ALL
SELECT'4','678','Apt 4','Winding Rd.' UNION ALL
SELECT'5','910','Rodeo Dr.',NULL UNION ALL
SELECT'6','121',NULL,NULL
)
SELECT *
FROM mySampleData
CROSS APPLY (
SELECT Addr1 + ' '
,Addr2 + ' '
,Addr3 + ' '
FOR XML PATH('')
) AS A(Addr)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply