Moving SQL data in columns

Question

I don’t know where to start, but here is what I need to do ...

We have a table with addresses and phone numbers. But I need to reduce the 6 columns of the phone number from 6 to 3. Moving numbers from right to left to any empty cells.

Example below -

What does the table look like

What I want it to look like

Answer 1

PIVOT and UNPIVOT will be able to complete the task. Idea:

• UNPIVOT to get data in rows
• Clear empty rows and count what new column will be
• PIVOT to return cleared data to columns.

Here is one way to do this using a bunch of CTEs in one statement. Note. I assumed that there is a column of identifiers, and I made up the table name:

 ;WITH Unpivoted AS ( -- our data into rows SELECT ID, TelField, Tel FROM Telephone UNPIVOT ( Tel FOR TelField IN (TEL01,TEL02,TEL03,TEL04,TEL05,TEL06) ) as up ), Cleaned AS ( -- cleaning the empty rows SELECT 'TEL0' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TelField) AS VARCHAR) [NewTelField], ID, TelField, Tel FROM Unpivoted WHERE NULLIF(NULLIF(Tel, ''), 'n/a') IS NOT NULL ), Pivoted AS ( -- pivoting back into columns SELECT ID, TEL01, TEL02, TEL03 FROM ( SELECT ID, NewTelField, Tel FROM Cleaned ) t PIVOT ( -- simply add ", TEL04, TEL05, TEL06" if you want to still see the -- other columns (or if you will have more than 3 final telephone numbers) MIN(Tel) FOR NewTelField IN (TEL01, TEL02, TEL03) ) pvt ) SELECT * FROM Pivoted ORDER BY ID 

This will translate the phone numbers to the right place at a time. You can also change the Pivoted in SELECT * FROM Pivoted to any of the other Unpivoted - Unpivoted , Cleaned - to see what the partial results will look like. Final result:

Answer 2

There are several ways to do this, the best choice depends on the database engine you are using and / or what other tools are available.

The dirtiest method may be to write 64 different sql statements that retrieve data in each of the 64 possible combinations (including where all fields are filled and all fields are filled 6). However, this may not be pleasant,

So the problem is finding a simple, repeatable algorithm to reduce the problem space to something simpler.

One way to do this is to write 6 statements, each of which, if possible, performs a shift to the left of one space.

eg.

 update table set field1 = field2, field2 = null from table where field2 is not null and field1 is null 

Moves content from field2 to field 1, if and only if field1 is empty and field 2 is not empty

Second statement:

 update table set field2 = field3, field3 = null from table where field3 is not null and field2 is null 

Does the same - and so on:

 update table set field3 = field4, field4 = null from table where field4 is not null and field3 is null update table set field4 = field5, field5 = null from table where field5 is not null and field4 is null update table set field5 = field6, field6 = null from table where field6 is not null and field2 is null 

Thus, that 5 operators as a whole - launching them as a package will gradually shift records by at least one batch - it will move more per batch in cases where the empty space on the left and all entries on the right are adjacent without spaces. Spaces will tend to move to the right, and so I assume that by running a batch at least 6 times, you should find yourself a dataset that matches what you want.

The actual sql you need will depend on how your RDBMS language deals with zeros or empty strings or something else, but the good news is that you only need 5 statements, and you only need to run them relatively a small number of times.

Probably smarter methods than this, but it's pretty simple, and each run of the package gives you one step closer to where you want to be. On the underside, this is a little awkward. If you need to run this periodically, I may be tempted to figure out something more elegant.

Depending on the type of data, you may need to run something that classifies / counts the number of records that you have for each combination / zero score. Thus, you may be able to reduce the number of runs that you have - most likely there are only 4 or 5 different templates, not the full 64.

Answer 3

SAMPLES DATA:

 IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp; SELECT * INTO #temp FROM (SELECT NULL TEL1, NULL TEL2, 1 TEL3, NULL TEL4, 2 TEL5,3 TEL6 UNION ALL SELECT 4 TEL1, NULL TEL2,5 TEL3, NULL TEL4, NULL TEL5,NULL TEL6 UNION ALL SELECT 6 TEL1, NULL TEL2,7 TEL3, NULL TEL4, NULL TEL5,NULL TEL6 UNION ALL SELECT NULL TEL1, 8 TEL2,9 TEL3, 10 TEL4, NULL TEL5,NULL TEL6 UNION ALL SELECT NULL TEL1, NULL TEL2,11 TEL3, NULL TEL4, 12 TEL5,NULL TEL6 UNION ALL SELECT NULL TEL1, 13 TEL2,14 TEL3, NULL TEL4, 15 TEL5,NULL TEL6 UNION ALL SELECT 16 TEL1, NULL TEL2,17 TEL3, NULL TEL4, NULL TEL5,18 TEL6 UNION ALL SELECT NULL TEL1, 19 TEL2,20 TEL3, NULL TEL4, NULL TEL5,21 TEL6 UNION ALL SELECT 22 TEL1, NULL TEL2,23 TEL3, NULL TEL4, 24 TEL5,NULL TEL6 UNION ALL SELECT NULL TEL1, 25 TEL2,26 TEL3, NULL TEL4, NULL TEL5,27 TEL6) AS A SELECT * FROM #temp 

DECISION:

 ;WITH CTE AS ( SELECT ID, 'TEL' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY PHONE) AS VARCHAR) AS PHONE,VALUE FROM (SELECT NEWID() AS ID,* FROM #temp) AS P UNPIVOT (Value FOR Phone IN (TEL1 ,TEL2, TEL3, TEL4, TEL5, TEL6 )) AS unpvt) SELECT [TEL1],[TEL2],[TEL3] FROM (SELECT * FROM CTE) P PIVOT (MAX(VALUE) FOR Phone IN ( [TEL1],[TEL2],[TEL3] ) ) AS pvt 

RESULT: