Shifting columns

Question

Shifting columns

David Lester

Hall of Fame

Points: 3647
More actions
May 19, 2008 at 2:59 pm

#71276

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…
RowIDCode1Code2Code3Code4
A112null18null
A2nullnullG9null
A3G1G3null87
A4nullnullnull96
I need it to end this way…
RowIDCode1Code2Code3Code4
A11218nullnull
A2G9nullnullnull
A3G1G387null
A496nullnullnull
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?

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply

raja_saminathan SSCommitted Points: 1607 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 2

David Lester (5/19/2008)
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…
RowIDCode1Code2Code3Code4
A112null18null
A2nullnullG9null
A3G1G3null87
A4nullnullnull96
I need it to end this way…
RowIDCode1Code2Code3Code4
A11218nullnull
A2G9nullnullnull
A3G1G387null
A496nullnullnull
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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Lester Hall of Fame Points: 3647 More actions · Answer 3

Thanks for the replies. I will take a look at the cross tab reporting and see if I can get it working.

Thanks again.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 4

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. Selburg

David Lester Hall of Fame Points: 3647 More actions · Answer 5

Rock on Jason, that is just what I was trying to come up with. I had not considered the case statement. :w00t:

Thanks!

David Lester Hall of Fame Points: 3647 More actions · Answer 6

Just finished adding the concept to my code Jason, works like magic.

Its just beautiful!! :w00t:

Sergiy SSC Guru Points: 110208 More actions · Answer 7

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

Sergiy SSC Guru Points: 110208 More actions · Answer 8

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

Anam Verma SSCertifiable Points: 5365 More actions · Answer 9

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 10

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. Selburg

David Lester Hall of Fame Points: 3647 More actions · Answer 11

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:

Sergiy SSC Guru Points: 110208 More actions · Answer 12

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

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 13

janine.rawnsley

Ten Centuries

Points: 1056

May 22, 2008 at 3:58 am

#819035

Is it possible for all four columns to be NULL?

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 14

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

)

SELECTRowID,

Value

FROM(

SELECTRowID,

1 AS Code,

Code1 AS Value

FROM@Sample

UNION ALL

SELECTRowID,

2,

Code2

FROM@Sample

UNION ALL

SELECTRowID,

3,

Code3

FROM@Sample

UNION ALL

SELECTRowID,

4,

Code4

FROM@Sample

) AS d

ORDER BYRowID,

CASE

WHEN Value IS NULL THEN 1

ELSE 0

END,

Value

SELECTs.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(

SELECTRowID,

MIN(RecID) AS RecID

FROM#Stage

GROUP BYRowID

) AS x ON x.RowID = s.RowID

GROUP BYs.RowID

ORDER BYs.RowID

DROP TABLE#Stage

N 56°04'39.16"
E 12°55'05.25"