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?
Advertisement
Answer
You’re almost there. Just a couple of tweaks.
- Replace the
@resultNm1in your CONCAT toRIGHT([u].[outcomes], 2)since you want the number from each of your columns. - Then change
All_Resultsto CONCAT what you did to get your ERP_ID field with All_ResultsCONCAT(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.