May 19, 2008 at 2:59 pm
This one has me baffled. I have a table with five columns. As follows
RowID Char(16) {Primary Key, Unique}
Code1 Char(6)
Code2 Char(6)
Code3 Char(6)
Code4 Char(6)
What I need to do is shift values from the last code column toward the first, if there are no values in the prior colums.
So example data:
Starts this way…
RowID Code1 Code2 Code3 Code4
A1 12 null 18 null
A2 null null G9 null
A3 G1 G3 null 87
A4 null null null 96
I need it to end this way…
RowID Code1 Code2 Code3 Code4
A1 12 18 null null
A2 G9 null null null
A3 G1 G3 87 null
A4 96 null null null
Any combination of columns having or not having a code is possible.
I started thinking of doing it in stages, like move Code4 to Code3 if code3 was null. That just seems like a non-set based solution. The way to pull this off is just not quite falling together for me.
Anyone know of a good (not RBAR) way to do this?
May 19, 2008 at 11:30 pm
Hi,
if you have only five columns you can use pivot table to change columns to rows and coalesce function will move to non null values up ,you will get the desired result
Rajesh
May 20, 2008 at 12:55 am
David Lester (5/19/2008)
This one has me baffled. I have a table with five columns. As followsRowID Char(16) {Primary Key, Unique}
Code1 Char(6)
Code2 Char(6)
Code3 Char(6)
Code4 Char(6)
What I need to do is shift values from the last code column toward the first, if there are no values in the prior colums.
So example data:
Starts this way…
RowID Code1 Code2 Code3 Code4
A1 12 null 18 null
A2 null null G9 null
A3 G1 G3 null 87
A4 null null null 96
I need it to end this way…
RowID Code1 Code2 Code3 Code4
A1 12 18 null null
A2 G9 null null null
A3 G1 G3 87 null
A4 96 null null null
Any combination of columns having or not having a code is possible.
I started thinking of doing it in stages, like move Code4 to Code3 if code3 was null. That just seems like a non-set based solution. The way to pull this off is just not quite falling together for me.
Anyone know of a good (not RBAR) way to do this?
David, since you've posted in a 2k forum, I'm going to assume that you really have 2k... that means PIVOT is not available. Look in Books Online in the Index for "Cross-tab reporting". That will do it for you. If you still don't understand after reading about that, come back and we'll work it out.
--Jeff Moden
May 20, 2008 at 8:27 am
Thanks for the replies. I will take a look at the cross tab reporting and see if I can get it working.
Thanks again.
May 20, 2008 at 8:37 am
No need to PIVOT or CROSSTAB, even though they'll both work ....
-- create a table for sample data
DECLARE @t TABLE
(RowID Char(16)
,Code1 Char(6)
,Code2 Char(6)
,Code3 Char(6)
,Code4 Char(6))
INSERT @t
SELECT 'A1', '12', NULL, '18', NULL UNION ALL
SELECT 'A2', NULL, NULL, 'G9', null UNION ALL
SELECT 'A3', 'G1', 'G3', null, '87' UNION ALL
SELECT 'A4', NULL, NULL, NULL, '96'
-- show the "before" data
SELECT * FROM @t
-- make the update
UPDATE @t
SET
Code1 = COALESCE(Code1,Code2,Code3,Code4)
,Code2 = CASE WHEN Code1 IS NULL THEN NULL ELSE COALESCE(Code2,Code3,Code4) END
,Code3 = CASE WHEN Code1 IS NULL OR Code2 IS NULL THEN NULL ELSE COALESCE(Code3,Code4) END
,Code4 = CASE WHEN Code1 IS NULL OR Code2 IS NULL OR Code3 IS NULL THEN NULL ELSE Code4 END
-- display the results
SELECT * FROM @t
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 20, 2008 at 8:48 am
Rock on Jason, that is just what I was trying to come up with. I had not considered the case statement. :w00t:
Thanks!
May 20, 2008 at 10:09 am
Just finished adding the concept to my code Jason, works like magic.
Its just beautiful!! :w00t:
May 20, 2008 at 4:37 pm
David Lester (5/20/2008)
Just finished adding the concept to my code Jason, works like magic.Its just beautiful!! :w00t:
You should improve your testing skills.
I can see logic holes in Jason's code, and it was not hard to find the case when it fails.
Try this:
-- create a table for sample data
DECLARE @t TABLE
(RowID Char(16)
,Code1 Char(6)
,Code2 Char(6)
,Code3 Char(6)
,Code4 Char(6))
INSERT @t
SELECT 'A1', '12', NULL, '18', NULL UNION ALL
SELECT 'A2', NULL, NULL, 'G9', 'G7' UNION ALL -- slight modification to the data
SELECT 'A3', 'G1', 'G3', null, '87' UNION ALL
SELECT 'A4', NULL, NULL, NULL, '96'
-- show the "before" data
SELECT * FROM @t
-- make the update
UPDATE @t
SET
Code1 = COALESCE(Code1,Code2,Code3,Code4)
,Code2 = CASE WHEN Code1 IS NULL THEN NULL ELSE COALESCE(Code2,Code3,Code4) END
,Code3 = CASE WHEN Code1 IS NULL OR Code2 IS NULL THEN NULL ELSE COALESCE(Code3,Code4) END
,Code4 = CASE WHEN Code1 IS NULL OR Code2 IS NULL OR Code3 IS NULL THEN NULL ELSE Code4 END
-- display the results
SELECT * FROM @t
_____________
Code for TallyGenerator
May 20, 2008 at 5:37 pm
To screw the test completely:
INSERT @t
SELECT 'A1', NULL, '12', NULL, '18' UNION ALL
SELECT 'A2', NULL, NULL, 'G9', 'G7' UNION ALL
SELECT 'A3', 'G1', null, 'G3', '87' UNION ALL
SELECT 'A4', NULL, '11', '96', NULL
Every record fails.
Perfect!
😛
_____________
Code for TallyGenerator
May 20, 2008 at 7:43 pm
There could be better ways to do it. Try this. I think i tried to cover every situation. 🙂
DECLARE @t TABLE
(RowID Char(16)
,Code1 Char(6)
,Code2 Char(6)
,Code3 Char(6)
,Code4 Char(6))
INSERT @t
SELECT 'A1', NULL, '12', NULL, '18' UNION ALL
SELECT 'A2', NULL, NULL, '29', '27' UNION ALL
SELECT 'A3', 'G3', NULL, '31', '37' UNION ALL
SELECT 'A4', NULL, '41', '46', NULL UNION ALL
SELECT 'A5', 'G5', '51', '59', '58' UNION ALL
SELECT 'A6', 'G6', NULL, '69', NULL UNION ALL
SELECT 'A7', 'G7', 'G71', NULL, NULL UNION ALL
SELECT 'A8', 'G8', NULL, NULL, 'G81'
-- show the "before" data
SELECT * FROM @t
-- make the update
UPDATE @t
SET
Code1 = COALESCE(Code1,Code2,Code3,Code4)
,Code2 = CASE
WHEN (Code1 IS NOT NULL AND Code2 is not NULL) THEN Code2
WHEN (Code1 IS NULL AND Code2 IS NOT NULL AND COALESCE(Code3,Code4) IS NULL) THEN NULL
WHEN (Code1 IS NULL AND Code2 IS NULL AND Code3 IS NOT NULL and Code4 IS NULL) THEN NULL
WHEN (Code1 IS NULL AND Code2 IS NULL AND Code3 IS NOT NULL and Code4 IS NOT NULL) THEN Code4
ELSE COALESCE(Code3,Code4)
END
,Code3 = CASE
WHEN (Code1 IS NULL AND Code2 IS NULL) THEN NULL
WHEN (Code1 IS NOT NULL AND Code2 IS NULL AND Code3 IS NOT NULL AND Code4 IS NOT NULL) THEN Code4
WHEN (Code1 IS NULL OR Code2 IS NULL) AND (Code3 IS NOT NULL OR Code4 IS NOT NULL) THEN NULL
ELSE Code3
END
,Code4 = CASE WHEN (Code1 IS NULL OR Code2 IS NULL OR Code3 IS NULL) THEN NULL ELSE Code4 END
SELECT * FROM @t
May 21, 2008 at 7:11 am
Good catch Sergiy 😛
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 21, 2008 at 8:14 am
Thanks Sergiy, this is what happens when I forget myself and allow the various directors demanding too many things to get too me. That being, I tested on too small of a sample of the data, however, things like this remind me to push back on the deadline hounds here, and that is a good thing. :Whistling:
May 21, 2008 at 10:36 pm
helloanam (5/20/2008)
There could be better ways to do it. Try this. I think i tried to cover every situation. 🙂
Solid approach, but too much typing, as for me.
😎
-- create a table for sample data
DECLARE @t TABLE
(RowID Char(16)
,Code1 Char(6)
,Code2 Char(6)
,Code3 Char(6)
,Code4 Char(6))
INSERT @t
SELECT 'A1', NULL, '12', NULL, '18' UNION ALL
SELECT 'A2', NULL, NULL, 'G9', 'G7' UNION ALL
SELECT 'A3', 'G1', null, 'G3', '87' UNION ALL
SELECT 'A4', NULL, '11', '96', NULL UNION ALL
SELECT 'A5', 'G5', '51', '59', '58' UNION ALL
SELECT 'A6', 'G6', NULL, '69', NULL UNION ALL
SELECT 'A7', 'G7', 'G71', NULL, NULL UNION ALL
SELECT 'A8', 'G8', NULL, NULL, 'G81'
-- show the "before" data
SELECT * FROM @t
-- make the update
UPDATE @t
SET
Code1 = COALESCE(Code1,Code2,Code3,Code4)
,Code2 = CASE
WHEN Code1 IS NOT NULL THEN COALESCE(Code2, Code3,Code4)
WHEN Code2 IS NULL THEN Code4
ELSE COALESCE(Code3,Code4)
END
,Code3 = CASE (select COUNT(C) from (select Code1 C UNION ALL select Code2 UNION ALL select Code3) DT )
WHEN 3 THEN Code3
WHEN 2 THEN Code4
ELSE NULL
END
,Code4 = CASE WHEN (Code1 IS NULL OR Code2 IS NULL OR Code3 IS NULL) THEN NULL ELSE Code4 END
-- display the results
SELECT * FROM @t
_____________
Code for TallyGenerator
May 22, 2008 at 3:58 am
Is it possible for all four columns to be NULL?
May 22, 2008 at 5:48 am
DECLARE @Sample TABLE (RowID CHAR(16) PRIMARY KEY CLUSTERED, Code1 CHAR(6), Code2 CHAR(6), Code3 CHAR(6), Code4 CHAR(6))
INSERT @Sample
SELECT 'A0', null, null, null, null UNION ALL
SELECT 'A1', '12', null, '18', null UNION ALL
SELECT 'A2', null, null, 'G9', null UNION ALL
SELECT 'A3', 'G1', 'G3', null, '87' UNION ALL
SELECT 'A4', null, null, null, '96'
SELECT *
FROM @Sample
CREATE TABLE #Stage
(
RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
RowID CHAR(16),
Value CHAR(6)
)
INSERT #Stage
(
RowID,
Value
)
SELECT RowID,
Value
FROM (
SELECT RowID,
1 AS Code,
Code1 AS Value
FROM @Sample
UNION ALL
SELECT RowID,
2,
Code2
FROM @Sample
UNION ALL
SELECT RowID,
3,
Code3
FROM @Sample
UNION ALL
SELECT RowID,
4,
Code4
FROM @Sample
) AS d
ORDER BY RowID,
CASE
WHEN Value IS NULL THEN 1
ELSE 0
END,
Value
SELECT s.RowID,
MAX(CASE WHEN s.RecID - x.RecID = 0 THEN s.Value ELSE NULL END) AS Code1,
MAX(CASE WHEN s.RecID - x.RecID = 1 THEN s.Value ELSE NULL END) AS Code2,
MAX(CASE WHEN s.RecID - x.RecID = 2 THEN s.Value ELSE NULL END) AS Code3,
MAX(CASE WHEN s.RecID - x.RecID = 3 THEN s.Value ELSE NULL END) AS Code4
FROM #Stage AS s
INNER JOIN (
SELECT RowID,
MIN(RecID) AS RecID
FROM #Stage
GROUP BY RowID
) AS x ON x.RowID = s.RowID
GROUP BY s.RowID
ORDER BY s.RowID
DROP TABLE #Stage
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply