SQL moving data along columns

Question

I have no idea where to even start with this, but here's what i need to do...

We have a table with addresses and telephone numbers in. But i need to reduce the 6 telephone number columns from 6 to 3. Moving the numbers along from right to left, into any empty cells.

Example below -

What the table looks like

What i want it to look like

Answer 1

PIVOT and UNPIVOT will be able to do the job. The idea:

1. UNPIVOT to get the data into rows
2. Clean out empty rows and calculate what the new column will be
3. PIVOT to get the cleaned data back into columns.

Here's one way of doing it using a bunch of CTEs in one statement. Note I've assumed there is an ID column and I've 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

That will shift the telephone numbers into their correct place in one go. You can also change the Pivoted in SELECT * FROM Pivoted to any of the other CTEs - Unpivoted, Cleaned - to see what the partial results would look like. Final result: