September 29, 2008 at 3:30 pm
I have a table
create table zipphone
(
zipcode varchar(6),
phone1 decimal(10,0),
phone2 decimal(10,0),
phone3 decimal(10,0),
phone4 decimal(10,0),
phone5 decimal(10,0),
phone6 decimal(10,0),
phone7 decimal(10,0),
phone8 decimal(10,0),
phone9 decimal(10,0),
phone10 decimal(10,0),
)
zipcode is the PK
my data looks like this
12345,null,1111111111,2222222222,3333333333,4444444444,555555555,6666666666,7777777777,8888888888,999999999
12346,null,null,2222222222,3333333333,4444444444,555555555,6666666666,7777777777,8888888888,999999999
12347,null,1111111111,2222222222,3333333333,null,555555555,6666666666,7777777777,8888888888,999999999
12348,null,null,2222222222,null,4444444444,555555555,6666666666,7777777777,8888888888,999999999
I need to use TSQL (no functions on SQL7) to end up with all of the phone numbers shifted down to move the nulls to the end or just have the last good number repeat at the end
12345,1111111111,2222222222,3333333333,4444444444,555555555,6666666666,7777777777,8888888888,999999999,null
12346,2222222222,3333333333,4444444444,555555555,6666666666,7777777777,8888888888,999999999,null,null
12347,1111111111,2222222222,3333333333,555555555,6666666666,7777777777,8888888888,999999999,null,null
12348,2222222222,4444444444,555555555,6666666666,7777777777,8888888888,999999999,null,null,null
September 29, 2008 at 7:36 pm
Wow! Someone either left you a heck of a mess, or you're trying to resolve a "hunt group" from an Avaya telephone switch (seriously, been there, done that). 😛
Hey folks, before you jump all over MrPoleCat with the normal lectures about normalization, lemme just say I know this guy and, unless he ate some really bad cat food, he wouldn't intentionally design a table like this and is probably in a position where he can't change it.
MrPoleCat... I believe I have a solution for you that will work very quickly even in SQL Server 7... it'll take me a bit to put it together.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 29, 2008 at 8:42 pm
OK... here we go... as usual, how the code works is explained as comments in the code...
/* Careful! This bit of code drops tables for test purposes! Could be a real one!
drop table dbo.ZipPhone
drop table #myhead
*/
go
--===== Create the test table (NOT part of the solution)
CREATE TABLE dbo.ZipPhone
(
ZipCode VARCHAR(6),
Phone1 DECIMAL(10,0),
Phone2 DECIMAL(10,0),
Phone3 DECIMAL(10,0),
Phone4 DECIMAL(10,0),
Phone5 DECIMAL(10,0),
Phone6 DECIMAL(10,0),
Phone7 DECIMAL(10,0),
Phone8 DECIMAL(10,0),
Phone9 DECIMAL(10,0),
Phone10 DECIMAL(10,0))
--===== Populate the test table with the data given in the post (NOT part of the solution)
INSERT INTO dbo.ZipPhone
(ZipCode,Phone1,Phone2,Phone3,Phone4,Phone5,Phone6,Phone7,Phone8,Phone9,Phone10)
SELECT 12345,null,1111111111,2222222222,3333333333,4444444444,555555555,6666666666,7777777777,8888888888,999999999 UNION ALL
SELECT 12346,null,null,2222222222,3333333333,4444444444,555555555,6666666666,7777777777,8888888888,999999999 UNION ALL
SELECT 12347,null,1111111111,2222222222,3333333333,null,555555555,6666666666,7777777777,8888888888,999999999 UNION ALL
SELECT 12348,null,null,2222222222,null,4444444444,555555555,6666666666,7777777777,8888888888,999999999
--===== SOLUTION STARTS HERE! =====
--===== Create a working table to hold normalized data for future denormalization
CREATE TABLE #MyHead
(ZipCode VARCHAR(6) NOT NULL,
Phone DECIMAL(10) NOT NULL,
OldCol TINYINT NOT NULL,
NewCol TINYINT NOT NULL)
--===== "Normalize" the data using an "old fashioned" method for "unpivoting" data
-- eliminating NULLs in the process AND keeping track of the "old" phone column number.
-- Using ">0" is a speed trick that's just a wee bit faster than IS NOT NULL.
INSERT INTO #MyHead
(ZipCode, Phone, OldCol, NewCol)
SELECT ZipCode, Phone1 ,1 ,0 FROM dbo.ZipPhone WHERE Phone1 > 0 UNION ALL
SELECT ZipCode, Phone2 ,2 ,0 FROM dbo.ZipPhone WHERE Phone2 > 0 UNION ALL
SELECT ZipCode, Phone3 ,3 ,0 FROM dbo.ZipPhone WHERE Phone3 > 0 UNION ALL
SELECT ZipCode, Phone4 ,4 ,0 FROM dbo.ZipPhone WHERE Phone4 > 0 UNION ALL
SELECT ZipCode, Phone5 ,5 ,0 FROM dbo.ZipPhone WHERE Phone5 > 0 UNION ALL
SELECT ZipCode, Phone6 ,6 ,0 FROM dbo.ZipPhone WHERE Phone6 > 0 UNION ALL
SELECT ZipCode, Phone7 ,7 ,0 FROM dbo.ZipPhone WHERE Phone7 > 0 UNION ALL
SELECT ZipCode, Phone8 ,8 ,0 FROM dbo.ZipPhone WHERE Phone8 > 0 UNION ALL
SELECT ZipCode, Phone9 ,9 ,0 FROM dbo.ZipPhone WHERE Phone9 > 0 UNION ALL
SELECT ZipCode, Phone10,10,0 FROM dbo.ZipPhone WHERE Phone10 > 0
--===== Here's the trick... we need this clustered index to support the
-- ranking we're going to do while maintaining the original order
-- of the phone numbers based on the "old" phone column number.
ALTER TABLE #MyHead
ADD PRIMARY KEY CLUSTERED (ZipCode,OldCol)
--===== Ok... all setup to do a "quirky update" to do some ranking...
-- Declare the variables we need to do the actual ranking.
-- The names of the variables pretty much tell all.
DECLARE @PrevZipCode VARCHAR(6)
DECLARE @PrevNewCol TINYINT
SET @PrevZipCode = ''
SET @PrevNewCol = 0
--===== Do the "quirky update" that will do "Ordinal Ranking" of the phone numbers
-- according to their original relative position to each other in the original data.
-- In other words, we're doing this to preserve the order in which the original
-- phone numbers appear in the original data, but vertically instead of horizontally.
-- Also, if you just wanted a "normalized" table, you could stop here.
-- Note that this avoids the hugely expensive "triangular join" method.
-- See the comments in the FROM clause for what maintains the "order of update".
UPDATE #MyHead
SET @PrevNewCol = NewCol = CASE WHEN ZipCode = @PrevZipCode THEN @PrevNewCol + 1 ELSE 1 END,
@PrevZipCode = ZipCode
FROM #MyHead WITH(INDEX(0)) --Forces the clustered index to be scanned in order for the update
--Note that this ONLY works on these "quirky updates" and cannot
--be depended on for SELECT's.
--===== This just shows what is meant by "Ordinal Ranking".
-- Look at the "NewCol" to see what I mean.
-- This is just for demonstration and is NOT part of the solution.
SELECT *
FROM #MyHead
ORDER BY ZipCode,NewCol
--===== Ok, we're all set... produce a result set that meets the requirement of shifting
-- all the phone numbers to the left, in their original order, so that all NULLs
-- end up on the far right.
-- This is what you call a "CrossTab" or "Pivot" and can be found in Books Online
SELECT ZipCode,
MAX(CASE WHEN NewCol = 1 THEN Phone END) AS Phone1,
MAX(CASE WHEN NewCol = 2 THEN Phone END) AS Phone2,
MAX(CASE WHEN NewCol = 3 THEN Phone END) AS Phone3,
MAX(CASE WHEN NewCol = 4 THEN Phone END) AS Phone4,
MAX(CASE WHEN NewCol = 5 THEN Phone END) AS Phone5,
MAX(CASE WHEN NewCol = 6 THEN Phone END) AS Phone6,
MAX(CASE WHEN NewCol = 7 THEN Phone END) AS Phone7,
MAX(CASE WHEN NewCol = 8 THEN Phone END) AS Phone8,
MAX(CASE WHEN NewCol = 9 THEN Phone END) AS Phone9,
MAX(CASE WHEN NewCol = 10 THEN Phone END) AS Phone10
FROM #MyHead
GROUP BY ZipCode
I wrote a couple-three articles, a while back, which explain, in much more detail, some of the techniques I used or avoided. Take a look...
[font="Arial Black"]Hidden RBAR: Triangular Joins [/font]
http://www.sqlservercentral.com/articles/T-SQL/61539/
[font="Arial Black"]Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5[/font]
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
[font="Arial Black"]Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/font]
http://www.sqlservercentral.com/articles/T-SQL/63681/
I've admittedly not been able to test these in SQL Server 7 because, well, I haven't used SQL Server 7 since the year 2000. So, I ask you a favor, please... please report back on whether or not all this stuff worked or not. If any errors occurred, please post them exactly as they appear so I stand a chance of fixing them without actually having SQL Server 7.
Also, to copy the code I posted, put your cursor just above the code box, click and drag to just below the code box, then copy. When you paste from that, everything should come out nicely formatted as it appears in the code box.
Thanks.:)
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply