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

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		…

The desired result:

Class	All_Results
ENG-NNN-01	ENG-NNN-01 data1
ENG-NNN-02	ENG-NNN-01 data2
…	…
MATH-NNN-02	MATH-NNN-02 data2
MATH-NNN-03	MATH-NNN-03 data3
MATH-NNN-04	MATH-NNN-04 data4

I have managed to get the table displayed properly, but getting multiple col_name and applying them to the proper place is beyond me.

DECLARE @resultNum1 varchar(2) = SUBSTRING(COL_NAME(OBJECT_ID('[TABLE_NAME]'), 20),9, 2)
DECLARE @ccOutcome varchar(4) = '-NNN'
DECLARE @class varchar (25)
SELECT CONCAT(class, @ccOutcome,'-', @resultNum1) AS 'ERP_ID', All_Results FROM [TABLE_NAME] AS tbl
UNPIVOT
(
    All_Results
    FOR outcomes IN( [Result01], [Result02], [Result03], [Result04], [Result05], [Result06], ...
)AS u
ORDER BY class;

Any ideas on how best to solve this?

Answer

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.