Shifting columns

Question

Shifting columns

Viewing 5 posts - 31 through 34 (of 34 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Jeff Moden (5/27/2008)
Even though we all, including the OP, recognize that this is a terrible requirement, has anyone actually come up with a solution that shifts the data left without resorting it? 😉

I believe yes.

Was it you who suggested to convert the data to normal form and then convert it back?

Or somebody else?

Does not matter.

Data must be normalized in order to be processed correctly. If they don't allow to make it static, then it will be 2 pivots - back and forward - instead of one.

That's the only approach I would consider to test.

Everything else is flat file processing, I do not do it at this stage of my life. 🙂

_____________
Code for TallyGenerator

sgmunson SSC Guru Points: 110640 More actions · Answer 2

There is a way to do this without sorting. I decided to try using REPLACE and ISNULL and an intermediate CTE, with a source table that has all 16 possible situations for NULL values, as follows:

DECLARE @TBL TABLE (

RowID Char(16),

Code1 Char(6),

Code2 Char(6),

Code3 Char(6),

Code4 Char(6)

)

-- Updated table to ensure all possible combinations of NULL with values

INSERT INTO @TBL

SELECT 'A01', NULL, NULL, NULL, NULL UNION ALL

SELECT 'A02', NULL, NULL, NULL, '27' UNION ALL

SELECT 'A03', NULL, NULL, '31', NULL UNION ALL

SELECT 'A04', NULL, NULL, '41', '45' UNION ALL

SELECT 'A05', NULL, '51', NULL, NULL UNION ALL

SELECT 'A06', NULL, '69', NULL, 'G6' UNION ALL

SELECT 'A07', NULL, 'G2', 'G8', NULL UNION ALL

SELECT 'A08', NULL, 'G3', 'G9', 'G0' UNION ALL

SELECT 'A09', 'G8', NULL, NULL, NULL UNION ALL

SELECT 'A10', 'G9', NULL, NULL, '18' UNION ALL

SELECT 'A11', 'G0', NULL, '29', NULL UNION ALL

SELECT 'A12', 'G3', NULL, '30', '31' UNION ALL

SELECT 'A13', 'G4', '41', NULL, NULL UNION ALL

SELECT 'A14', 'G5', '51', NULL, '59' UNION ALL

SELECT 'A15', 'G6', '61', '69', NULL UNION ALL

SELECT 'A16', 'G7', 'G2', '70', '86'

-- show the "before" data

SELECT * FROM @TBL

;WITH INTERMEDIATE AS (

SELECT RowID, REPLACE(ISNULL(CAST(Code1 AS char(6)),'NULL ') +

ISNULL(CAST(Code2 AS char(6)),'NULL ') +

ISNULL(CAST(Code3 AS char(6)),'NULL ') +

ISNULL(CAST(Code4 AS char(6)),'NULL '),'NULL ','') AS TEXT_CONTENT

FROM @TBL

)

-- make the update

UPDATE @TBL

SET Code1 = COALESCE(Code1, Code2, Code3, Code4),

Code2 = CASE WHEN RTRIM(SUBSTRING(TEXT_CONTENT,7,6)) = '' THEN NULL

ELSE RTRIM(SUBSTRING(TEXT_CONTENT,7,6)) END,

Code3 = CASE WHEN RTRIM(SUBSTRING(TEXT_CONTENT,13,6)) = '' THEN NULL

ELSE RTRIM(SUBSTRING(TEXT_CONTENT,13,6)) END,

Code4 = CASE WHEN RTRIM(SUBSTRING(TEXT_CONTENT,19,6)) = '' THEN NULL

ELSE RTRIM(SUBSTRING(TEXT_CONTENT,19,6)) END

FROM @TBL AS A INNER JOIN INTERMEDIATE AS B

ON A.RowID=B.RowID

SELECT * FROM @TBL

The results checked out.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

Nicely done, Steve... Because of the nature of the CHAR datatype and the fact that the orginal table uses it instead of VARCHAR, you can really shorten the code up. Also, since this is an SQL Server 2000 forum, I've made the code compatible with both SQL Server 2000 and 2005. Here's the full code including the test data you were kind enough to provide...

DECLARE @Tbl TABLE

(

RowID Char(16),

Code1 Char(6),

Code2 Char(6),

Code3 Char(6),

Code4 Char(6)

)

-- Updated table to ensure all possible combinations of NULL with values

INSERT INTO @Tbl

SELECT 'A01', NULL, NULL, NULL, NULL UNION ALL

SELECT 'A02', NULL, NULL, NULL, '27' UNION ALL

SELECT 'A03', NULL, NULL, '31', NULL UNION ALL

SELECT 'A04', NULL, NULL, '41', '45' UNION ALL

SELECT 'A05', NULL, '51', NULL, NULL UNION ALL

SELECT 'A06', NULL, '69', NULL, 'G6' UNION ALL

SELECT 'A07', NULL, 'G2', 'G8', NULL UNION ALL

SELECT 'A08', NULL, 'G3', 'G9', 'G0' UNION ALL

SELECT 'A09', 'G8', NULL, NULL, NULL UNION ALL

SELECT 'A10', 'G9', NULL, NULL, '18' UNION ALL

SELECT 'A11', 'G0', NULL, '29', NULL UNION ALL

SELECT 'A12', 'G3', NULL, '30', '31' UNION ALL

SELECT 'A13', 'G4', '41', NULL, NULL UNION ALL

SELECT 'A14', 'G5', '51', NULL, '59' UNION ALL

SELECT 'A15', 'G6', '61', '69', NULL UNION ALL

SELECT 'A16', 'G7', 'G2', '70', '86'

-- show the "before" data

SELECT * FROM @Tbl

--===== Do the update while parsing the 6 character "slots"

UPDATE @Tbl

SET Code1 = NULLIF(SUBSTRING(b.TextContent, 1,6),''),

Code2 = NULLIF(SUBSTRING(b.TextContent, 7,6),''),

Code3 = NULLIF(SUBSTRING(b.TextContent,13,6),''),

Code4 = NULLIF(SUBSTRING(b.TextContent,19,6),'')

FROM @Tbl a

INNER JOIN

(--==== Shift the data left in the natural 6 character "slots"

SELECT RowID,

REPLACE(

ISNULL(Code1,'')

+ ISNULL(Code2,'')

+ ISNULL(Code3,'')

+ ISNULL(Code4,'')

,SPACE(6),'') AS TextContent

FROM @Tbl) b

ON a.RowID = b.RowID

SELECT * FROM @Tbl

--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)

sgmunson SSC Guru Points: 110640 More actions · Answer 4

Yes, Jeff... you're quite right. Being a newbie to both SQL Server 2005 (although an oldie to SQL Server 2000) as well as this forum, my excitement at finding a simple solution that uses my new skills overcame my usual caution in keeping my comments relevant to the audience. Thanks for translating and for demonstrating a great use of NULLIF. I haven't had ANY opportunity to use it before, and here's a nearly ideal situation for it.