Consolidating multiple fields within one table-CodePudding

Hello Stack Overflow community,

I would like to consolidate multiple fields within the same table. Among other fields,'Table1' contains a number of fields each for a different US state. For a given 'ID' (primary key), a US state field will return a '1' for the corresponding state to that ID and will return 'null' for the rest of the state fields. Any given state may be applicable for multiple ID's.

Specifically, I would like to consolidate all the state fields into one field that will return the corresponding state for a given ID instead of having an unnecessary amount of state fields that will make the data harder to work with. I've visually outlined the existing format and desired format below. Any help is greatly appreciated!

Visual Examples of Existing and Desired Format.

I tried the following which did not work in creating the desired format:

INSERT INTO Table1 (ID, Cust_St_NY, Cust_St_IL, Cust_St_...) SELECT GROUP CONCAT (ID, Cust_St_NY, Cust_St_IL, Cust_St_...) FROM Table1 GROUP BY ID

CodePudding user response：

I'd use a CASE statement (but, I probably overuse them)

For the Cust_St Column:

CASE WHEN Cust_ST_NY = 1 THEN 'NY' WHEN Cust_ST_IL = 1 THEN 'IL' ELSE NULL END

Here's the Teradata specific docs: https://docs.teradata.com/r/zzfV8dn~lAaKSORpulwFMg/j8_MiWcAgH1twjaVyLkeCg

CodePudding user response：

If there's exactly one state set to 1 you could utilize UNPIVOT like this:

select id, substring(state from 9) as state, othercolumns
from vt
unpivot
 (       -- state contains the column name
   flag for state in (Cust_St_NY,Cust_St_IL,Cust_St_WY,...)
 ) as dt

UNPIVOT excludes nulls by default, resulting in a si⬇︎ngle row per ID.

I don't know about performance, but I expect @bph's CASE to perform better. Both require a list of states, which can easily be created based on dbc.ColumnsV.