tsql - Retrieve substring of a column name, then apply them to corresponding column data? SQL

Question

I have a table with, let's say, 13 columns. I need to take a part of the column name and apply it to the corresponding data in two columns.

Example of what I start with:

Class	Result01	Result02	Result03	Result04	Result05	Result06	...
ENG	data1	data2	data3	data4	data5	data6	...
MATH		data2	data3	data4	data5		...

question from:https://stackoverflow.com/questions/65907247/retrieve-substring-of-a-column-name-then-apply-them-to-corresponding-column-dat

Answer 1

You're almost there. Just a couple of tweaks.

• Replace the @resultNm1 in your CONCAT to RIGHT([u].[outcomes], 2) since you want the number from each of your columns.
• Then change All_Results to CONCAT what you did to get your ERP_ID field with All_Results CONCAT(CONCAT(class, @ccOutcome,'-', RIGHT([u].[outcomes], 2)), ' ', All_Results) with a space in there.

Your query would then look like:

SELECT   CONCAT([Class], @ccOutcome, '-', RIGHT([u].[outcomes], 2)) AS 'ERP_ID'
       , CONCAT(CONCAT([Class], @ccOutcome, '-', RIGHT([u].[outcomes], 2)), ' ', [All_Results]) AS [All_Results]
FROM     [TABLE_NAME] AS [tbl]
    UNPIVOT (
                [All_Results]
                FOR [outcomes] IN ( [Result01], [Result02], [Result03], [Result04], [Result05], [Result06] )
            ) AS [u]
ORDER BY [u].[Class];

Which then should give you the following results based on your sample data:

ERP_ID            All_Results
----------------- ----------------------------
ENG-NNN-01        ENG-NNN-01 data1
ENG-NNN-02        ENG-NNN-02 data2
ENG-NNN-03        ENG-NNN-03 data3
ENG-NNN-04        ENG-NNN-04 data4
ENG-NNN-05        ENG-NNN-05 data5
ENG-NNN-06        ENG-NNN-06 data6
MATH-NNN-01       MATH-NNN-01 
MATH-NNN-02       MATH-NNN-02 data2
MATH-NNN-03       MATH-NNN-03 data3
MATH-NNN-04       MATH-NNN-04 data4
MATH-NNN-05       MATH-NNN-05 data5
MATH-NNN-06       MATH-NNN-06 

I would suggest revisiting your design and what you are trying to accomplish if possible. Continuing patterns like this can cause challenges trying to get results.