April 10, 2012 at 8:30 am
Hi, does anyone know of an easy/direct way of doing this? I have three address fields: addr1, addr2, addr3. It's possible that any of the fields could or could not have a value.
For example
id, addr1, addr2, addr3
1, 123, NULL, Main St.
2, NULL, NULL, NULL
3, NULL, 345, Elm St.
4, 678, Apt 4, Winding Rd.
5, 910, Rodeo Dr., NULL
6 121, NULL, NULL
I'm trying to shift all values to the left where they can be shifted (where there's a NULL). So, based on the example above:
id, addr1, addr2, addr3
1, 123, Main St., NULL
2, NULL, NULL, NULL
3, 345, Elm St., NULL
4, 678, Apt 4, Winding Rd.
5, 910, Rodeo Dr., NULL
6 121, NULL, NULL
I can use COALESCE for addr1, but I don't think it will work properly for addr2 and addr3. Any other ideas? It might have to be some crazy CASE statements.
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
April 10, 2012 at 8:38 am
If you have only 3 fields just do 3 simple case statements. Shouldn't be that much code. Can't think offhand how to do it better.
Jared
CE - Microsoft
April 10, 2012 at 8:50 am
It's not that I mind using three case statements. It's that I don't think they'll be that simple. How about with concatenation?
Mike Scalise, PMP
https://www.michaelscalise.com
April 10, 2012 at 8:55 am
Maybe I am misunderstanding you. You stated
I'm trying to shift all values to the left where they can be shifted (where there's a NULL).
You do not mention any concatenation.
Jared
CE - Microsoft
April 10, 2012 at 8:59 am
Yes, that's my ultimate goal. I'm just trying to think of ways I can accomplish that. For example, I could probably use a combination of ISNULL() with concatenation with a delimiter and the substring function to shift values and end up with the result set I'm looking for. Just a thought, but I'm sure there has to be a better way.
Mike Scalise, PMP
https://www.michaelscalise.com
April 10, 2012 at 9:03 am
Is this going to be a regular operation, or are you trying to clean existing data where new constraints have been placed in the application that is inserting the data (or the user entering it). How many rows are we talking about?
Jared
CE - Microsoft
April 10, 2012 at 9:04 am
This will probably end up being a regular operation.
Mike Scalise, PMP
https://www.michaelscalise.com
April 10, 2012 at 9:06 am
The other option is to do this in separate queries starting with checking col 3 and moving data left. Then column 2.
Jared
CE - Microsoft
April 10, 2012 at 9:09 am
something like this seems to work, using the Case Statement logic Jaren mentioned:
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 id,
CASE
WHEN ADDR1 IS NOT NULL THEN ADDR1
ELSE CASE
WHEN ADDR2 IS NOT NULL THEN ADDR2
ELSE CASE
WHEN ADDR3 IS NOT NULL THEN ADDR3
ELSE NULL
END
END
END AS ADDRESS1,
CASE
WHEN ADDR2 IS NOT NULL
AND ADDR1 IS NOT NULL THEN ADDR2
WHEN ADDR2 IS NOT NULL
AND ADDR1 IS NULL THEN ADDR3
WHEN ADDR2 IS NULL
AND ADDR3 IS NOT NULL THEN ADDR3
WHEN ADDR2 IS NULL
AND ADDR1 IS NULL THEN ADDR3
ELSE CASE
WHEN ADDR3 IS NOT NULL THEN ADDR3
ELSE NULL
END
END AS ADDRESS2,
CASE
WHEN ADDR3 IS NOT NULL
AND ADDR2 IS NOT NULL
AND ADDR1 IS NOT NULL THEN ADDR3
ELSE NULL
END AS ADDRESS3
FROM mySampleData
Lowell
April 10, 2012 at 9:10 am
SQLKnowItAll (4/10/2012)
The other option is to do this in separate queries starting with checking col 3 and moving data left. Then column 2.
That's the ticket, but only one query is necessary.
It goes something like this:
http://www.sqlservercentral.com/Forums/FindPost1139268.aspx
EDIT: lots of posts in a short time...forum quoted wrong post for me
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 10, 2012 at 9:14 am
Try this!
DECLARE @Example
TABLE (
row_id INTEGER IDENTITY PRIMARY KEY,
address1 VARCHAR(50) NULL,
address2 VARCHAR(50) NULL,
address3 VARCHAR(50) NULL,
address4 VARCHAR(50) NULL,
address5 VARCHAR(50) NULL,
address6 VARCHAR(50) NULL,
address7 VARCHAR(50) NULL
);
INSERT @Example
(address1, address2, address3, address4, address5, address6, address7)
VALUES ('House 1', 'A Road', '', 'Some Place', 'Some Town', '', 'Post Code 1');
INSERT @Example
(address1, address2, address3, address4, address5, address6, address7)
VALUES ('House 2', '', 'Another Road', '', 'Some Other Town', 'Post Code 2', '');
SELECT * FROM @Example
;WITH Relational
AS (
-- Unpivot the data into a more relational form
-- Only return non-empty address entries
-- Renumber the addresses in non-empty order
SELECT U.row_id,
name = 'address' + CONVERT(CHAR(1),
ROW_NUMBER() OVER (PARTITION BY U.row_id ORDER BY U.name)),
U.value
FROM @Example E
UNPIVOT (
value
FOR name
IN (E.address1, E.address2, E.address3, E.address4, E.address5, E.address6, E.address7)
) U
WHERE U.value <> SPACE(0)
)
-- Pivot back
SELECT P.row_id,
address1 = ISNULL(P.address1, SPACE(0)),
address2 = ISNULL(P.address2, SPACE(0)),
address3 = ISNULL(P.address3, SPACE(0)),
address4 = ISNULL(P.address4, SPACE(0)),
address5 = ISNULL(P.address5, SPACE(0)),
address6 = ISNULL(P.address6, SPACE(0)),
address7 = ISNULL(P.address7, SPACE(0))
FROM Relational R
PIVOT (
MAX(R.value)
FOR R.name
IN (address1, address2, address3, address4, address5, address6, address7)
) P
ORDER BY
P.row_id ASC;
Instead of NULL, the sample data i have contains SPACE(0). Replace that by NULL and u are good to go.
April 10, 2012 at 9:24 am
And yet another way to skin this cat...
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
select * from #Address
update #Address set
addr1 = COALESCE(addr1, addr2, addr3),
addr2 = case
when addr1 IS null
then addr3
else coalesce(addr2, addr3)
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 9:30 am
opc.three (4/10/2012)
SQLKnowItAll (4/10/2012)
The other option is to do this in separate queries starting with checking col 3 and moving data left. Then column 2.That's the ticket, but only one query is necessary.
It goes something like this:
http://www.sqlservercentral.com/Forums/FindPost1139268.aspx
EDIT: lots of posts in a short time...forum quoted wrong post for me
Wow, I forgot all about this. Plenty of solutions there. Thanks, opc!
Also, thanks to everyone else who responded and proposed solutions.
Mike Scalise, PMP
https://www.michaelscalise.com
April 10, 2012 at 9:32 am
Or this?
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);
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
April 10, 2012 at 9:39 am
Sean Lange (4/10/2012)
And yet another way to skin this cat...
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
select * from #Address
update #Address set
addr1 = COALESCE(addr1, addr2, addr3),
addr2 = case
when addr1 IS null
then addr3
else coalesce(addr2, addr3)
end,
addr3 = case
when addr1 IS null then null
when addr2 IS null then null
else addr3
end
select * from #Address
DROP TABLE #Address
Sean, I think this breaks if the first two address fields have a value and the third doesnt:
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 Rd.'
select * from #Address
update #Address set
addr1 = COALESCE(addr1, addr2, addr3),
addr2 = case
when addr1 IS null
then addr3
else coalesce(addr2, addr3)
end,
addr3 = case
when addr1 IS null then null
when addr2 IS null then null
else addr3
end
select * from #Address
DROP TABLE #Address
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply