I have a weird data issue at work someone asked me to help with and I'm trying to think of the best way to handle it, but have come up with nada so far.

I have a table that looks similar to the example below. In actuality, it extends out to 15 columns (or levels), but that's probably not important here. For each unique id, the hierarchy can differ which would result in some of the columns being null. So for example below, for id = 1, this only has 3 levels associated with it, so columns Level4 and Level5 are null.
_____________________________________________
ID | Level 1 | Level 2 | Level 3 | Level 4 | Level 5
1 | John | Steve | Sue
2 | Tom | Rick | Mary | Tim
3 | Kay | Paul | Jill | John | Steve
4 | Will | Larry | Jack
_____________________________________________

The question is whether there is a way to reverse the order of the columns. For example, for id=1, I would like to have level1 = Sue, level2 = Steve, level 3=John, level4 is null and level 5 is null.

_____________________________________________
ID | Level 1 | Level 2 | Level 3 | Level 4 | Level 5
1 | Sue | Steve | John
2 | Tim | Mary | Rick | Tom
3 | Steve | John | Jill | Paul | Kay
4 | Jack | Larry | Will
_____________________________________________

I have been wondering if there is a way I could coalesce into a single string, and then parse it out in reverse order into separate columns, but not sure. Anyone have any bright ideas?

Ask chatgpt or bard and see what it says. That's the new way of doing things.

aggie_wes said:

---

Ask chatgpt or bard and see what it says. That's the new way of doing things.

---

Ha. Good point. I will advance the process of rendering myself useless.

It seems to me that table should be designed like this:

ID|Level|Name
1|1|John
1|2|Steve
1|3|Sue
2|1|Tom
2|2|Rick
2|3Mary
2|4|Tim

Then you would select like this:


SELECT * FROM table_name ORDER BY ID ASC, Level ASC;
SELECT * FROM table_name ORDER BY ID ASC, Level DESC;


Or if you can't modify the structure of the existing table, extract the data to a temporary table structured like above.

TAMU-93 said:

---

It seems to me that table should be designed like this:

ID|Level|Name
1|1|John
1|2|Steve
1|3|Sue
2|1|Tom
2|2|Rick
2|3Mary
2|4|Tim

Then you would select like this:


SELECT * FROM table_name ORDER BY ID ASC, Level ASC;
SELECT * FROM table_name ORDER BY ID ASC, Level DESC;


Or if you can't modify the structure of the existing table, extract the data to a temporary table structured like above.

---

Yep. It's not designed as it needs to be. But it's their source and I can just hit it for reporting. I could handle this in a one time report fairly easy, but for a daily automated approach where I have to work off the current table, I need to find a way to work with it.

Is this in an Oracle db? If so, you can use UNPIVOT and PIVOT like this:

Quote:

---

WITH levels_start AS
(SELECT id
,level_desc
,name
FROM emp_levels
UNPIVOT (name FOR level_desc IN (level1
,level2
,level3
,level4
,level5
)
)
ORDER BY id
,level_desc DESC
)
,levels_next AS
(SELECT id
,name
,'LEVEL' || ROW_NUMBER() OVER(PARTITION BY id ORDER BY level_desc DESC) AS level_sorted
FROM levels_start
)
SELECT *
FROM levels_next
PIVOT (ANY_VALUE(name) FOR level_sorted IN ('LEVEL1' AS level1
,'LEVEL2' AS level2
,'LEVEL3' AS level3
,'LEVEL4' AS level4
,'LEVEL5' AS level5
)
)

---

I loaded your example data into a table called "emp_levels". The first common table expression (CTE), "levels_start", unpivots the data from columns into a row structure similar to what TAMU-93 suggested. The 2nd CTE, "levels_next", reorders the unpivoted data, and then the last query block pivots it into the desired output (rows back into columns).

Short of a sproc, I think the best way to do this would use a CASE expression, assuming you database supports it.

Something like this:

SELECT ID,
CASE
WHEN level5 IS NOT NULL THEN level5
WHEN level4 IS NOT NULL THEN level4
WHEN level3 IS NOT NULL THEN level3
WHEN level2 IS NOT NULL THEN level2
WHEN level1 IS NOT NULL THEN level1
ELSE NULL
END AS level1,

CASE
WHEN level5 IS NOT NULL THEN level4
WHEN level4 IS NOT NULL THEN level3
WHEN level3 IS NOT NULL THEN level2
WHEN level2 IS NOT NULL THEN level1
ELSE NULL
END AS level2,

CASE
WHEN level5 IS NOT NULL THEN level3
WHEN level4 IS NOT NULL THEN level2
WHEN level3 IS NOT NULL THEN level1
ELSE NULL
END AS level3,

CASE
WHEN level5 IS NOT NULL THEN level2
WHEN level4 IS NOT NULL THEN level1
ELSE NULL
END AS level4,

CASE
WHEN level5 IS NOT NULL THEN level1
ELSE NULL
END AS level5

FROM myTable

MSSQL per gpt4: You can achieve this by using a combination of the COALESCE function and a temporary table. Here's a step-by-step approach to do it in SQL:

1. First, create a temporary table to hold the reversed data:

```sql
CREATE TEMPORARY TABLE temp_table AS (
SELECT
ID,
COALESCE(Level5, Level4, Level3, Level2, Level1) AS New_Level1,
NULL AS New_Level2,
NULL AS New_Level3,
NULL AS New_Level4,
NULL AS New_Level5
FROM original_table
);
```

2. Update the temporary table to fill in the remaining levels:

```sql
UPDATE temp_table
SET
New_Level2 = COALESCE(Level4, Level3, Level2, Level1),
New_Level3 = COALESCE(Level3, Level2, Level1),
New_Level4 = COALESCE(Level2, Level1),
New_Level5 = Level1
FROM original_table
WHERE temp_table.ID = original_table.ID;
```

3. Now you can select the data from the temporary table with the desired format:

```sql
SELECT ID, New_Level1 AS Level1, New_Level2 AS Level2, New_Level3 AS Level3, New_Level4 AS Level4, New_Level5 AS Level5
FROM temp_table;
```

The result will have the levels reversed as you described in your example. This approach is scalable and works for any number of levels. Just make sure to adjust the COALESCE functions and update statements to accommodate the additional columns.

Sorry, but I don't think the CASE and COALESCE suggestions above work. For ID=1 from the OP, I think the result will be:

Quote:

---

Sue | Sue | Sue | Steve | John

---

Level4 and Level5 are both NULL in that row initially:

Quote:

---

John | Steve | Sue | NULL | NULL

---

The first CASE/COALESCE will get "Sue" from Level3, because Level4 and Level5 are NULL.
The second CASE/COALESCE will also get "Sue" because Level4 is NULL.
The third CASE/COALESCE will get "Sue" because it starts at Level3 which is not NULL.

GrapevineAg said:

---

Sorry, but I don't think the CASE and COALESCE suggestions above work. For ID=1 from the OP, I think the result will be:

Quote:

---

Sue | Sue | Sue | Steve | John

---

Level4 and Level5 are both NULL in that row initially:

Quote:

---

John | Steve | Sue | NULL | NULL

---

The first CASE/COALESCE will get "Sue" from Level3, because Level4 and Level5 are NULL.
The second CASE/COALESCE will also get "Sue" because Level4 is NULL.
The third CASE/COALESCE will get "Sue" because it starts at Level3 which is not NULL.

---

In each CASE, the "WHEN level3 IS NOT NULL" will be satisfied (or ELSE NULL if not there).

So the results for all CASE expressions are correct (in parentheses):

CASE for level1:
WHEN level3 IS NOT NULL THEN level3 (Sue)

CASE for level2:
WHEN level3 IS NOT NULL THEN level2 (Steve)

CASE for level3:
WHEN level3 IS NOT NULL THEN level1 (John)

CASE for level4:
ELSE NULL (null)

CASE for level5:
ELSE NULL (null)

rynning said:

---

In each CASE, the "WHEN level3 IS NOT NULL" will be satisfied (or ELSE NULL if not there).

So the results for all CASE expressions are correct (in parentheses):

CASE for level1:
WHEN level3 IS NOT NULL THEN level3 (Sue)

CASE for level2:
WHEN level3 IS NOT NULL THEN level2 (Steve)

CASE for level3:
WHEN level2 IS NOT NULL THEN level1 (John)

CASE for level4:
ELSE NULL (null)

CASE for level5:
ELSE NULL (null)

---

My apologies, you are correct. I mis-read the 2nd-5th CASE expressions. (that's what I get for coding after drinking Pacificos all afternoon and evening)

GrapevineAg, rynning, corndog04... thanks for all your time on this. I wish I could buy you all some Pacificos.

GrapevineAg, this is Redshift. I took a quick look, and it appears to support PIVOT and UNPIVOT. I will definitely look into this.

rynning, using CASE was the first thing I thought of, but was looking to see if there was something more elegant. If I cant get anything else figured out, I will probably go with this. Thanks for the illustration.

corndog04, I hadnt had the chance to check out chatGPT yet. The result is scarier than I thought. Thanks!

I think I will play around with all three solutions and see what I can get to work. I will let you know how it goes. Thanks for your time with this.

I have to agree with TAMU-93: Having many numeric-suffixed columns in a SQL table is a code smell.

But if you're stuck with the existing table structure, you can work around it, by using a VIEW to reformat it how it should have been.

CREATE VIEW NormalizedTable AS
SELECT ID, 1 AS Level, Level1 AS Name FROM ExampleTable WHERE Level1 IS NOT NULL
UNION
SELECT ID, 2 AS Level, Level2 AS Name FROM ExampleTable WHERE Level2 IS NOT NULL
UNION
SELECT ID, 3 AS Level, Level3 AS Name FROM ExampleTable WHERE Level3 IS NOT NULL
UNION
SELECT ID, 4 AS Level, Level4 AS Name FROM ExampleTable WHERE Level4 IS NOT NULL
UNION
SELECT ID, 5 AS Level, Level5 AS Name FROM ExampleTable WHERE Level5 IS NOT NULL;

if it were mine Id split it up into multiple tables and slap a couple inner joins in there

GrapevineAg said:

---

Is this in an Oracle db? If so, you can use UNPIVOT and PIVOT like this:

Quote:

---

WITH levels_start AS
(SELECT id
,level_desc
,name
FROM emp_levels
UNPIVOT (name FOR level_desc IN (level1
,level2
,level3
,level4
,level5
)
)
ORDER BY id
,level_desc DESC
)
,levels_next AS
(SELECT id
,name
,'LEVEL' || ROW_NUMBER() OVER(PARTITION BY id ORDER BY level_desc DESC) AS level_sorted
FROM levels_start
)
SELECT *
FROM levels_next
PIVOT (ANY_VALUE(name) FOR level_sorted IN ('LEVEL1' AS level1
,'LEVEL2' AS level2
,'LEVEL3' AS level3
,'LEVEL4' AS level4
,'LEVEL5' AS level5
)
)

---

I loaded your example data into a table called "emp_levels". The first common table expression (CTE), "levels_start", unpivots the data from columns into a row structure similar to what TAMU-93 suggested. The 2nd CTE, "levels_next", reorders the unpivoted data, and then the last query block pivots it into the desired output (rows back into columns).

---

UNPIVOT and PIVOT are what I would recommend too. They were basically designed for this exact use case.

Is there a single SELECT statement that will work with an arbitrary number of columns or in ISO SQL?

What did you end up doing?