Shifting columns

Question

Post reply

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…

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?

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

raja_saminathan

SSCommitted

Points: 1607

May 19, 2008 at 11:30 pm

#817576

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: 1000539 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…

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

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)
Intro to Tally Tables and Functions

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

David Lester

Hall of Fame

Points: 3647

May 20, 2008 at 8:27 am

#817951

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

David Lester

Hall of Fame

Points: 3647

May 20, 2008 at 8:48 am

#817981

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

David Lester

Hall of Fame

Points: 3647

May 20, 2008 at 10:09 am

#818055

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

Its just beautiful!! :w00t:

Sergiy SSC Guru Points: 110192 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: 110192 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

Jason Selburg

SSC-Insane

Points: 24641

May 21, 2008 at 7:11 am

#818538

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

David Lester

Hall of Fame

Points: 3647

May 21, 2008 at 8:14 am

#818586

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: 110192 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: 39725 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

		)

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"