How will I achieve the following requirement using sql function or query

Question

How will I write a sql function for the below requirements

I have a temp table table1 with all columns of int type

                   table1

scoreA |  scoreB | scoreC | classA |classB | classC |

suppose I have another temp table2 table with both of nvarchar column type

                   table2

        _______name__________myFormat_________
          ScoresOfAToC  |  1:10,2:20,3:30 |
           ClassOfAToC  |    1:4,2:5,3:6  |

How will I split myFormat column and insert data from table2 to table 1 such that my final output should be something like below:

                    table1

scoreA |  scoreB | scoreC | classA |classB | classC |
  10        20      30        4       5        6

You do know that this is not how you should use a database? There should probably be one table consisting of two columns, class and score holding the data. — Thorsten Kettner, May 22 '19 at 06:13
As to the problem: I'd use a programming language. Select the data from table2, then use loops to extract it and fill table1. — Thorsten Kettner, May 22 '19 at 06:15
You have that fixed 2 row in table2? or more rows are there? If more rows are there, please add more sample rows. — mkRabbani, May 22 '19 at 07:13
@mkRabbani :As of now i need a sample code which can do these conversion. In fact It has more rows but I will take the logic from here and extend it to other rows as well. — krity, May 22 '19 at 07:22

mkRabbani · Answer 1 · 2019-05-22T13:07:07.533

As you mentioned you only need the logic of splitting required values from a combined value, you can use SUBSTRING with the help of CHARINDEX to get your desired results. Some sample codes are given below-

SELECT name,
SUBSTRING(myFormat, (CHARINDEX(':',myFormat, 0))+1,(CHARINDEX(',',myFormat, 0))-((CHARINDEX(':',myFormat, 0))+1)),
SUBSTRING(myFormat, (CHARINDEX(':',myFormat,(CHARINDEX(':',myFormat,0))+1))+1,(CHARINDEX(',',myFormat, (CHARINDEX(',',myFormat, 0))+1))-((CHARINDEX(':',myFormat,(CHARINDEX(':',myFormat,0))+1))+1)),
SUBSTRING(myFormat,CHARINDEX(':',myFormat,CHARINDEX(':',myFormat,(CHARINDEX(':',myFormat,0))+1)+1)+1,LEN(myFormat))
FROM table2

The output of this script for your sample data is as below. Hope you can now adjust this logic in your script.

ScoresOfAToC    10  20  30
ClassOfAToC      4   5   6

To get everything in one line, Apply a PIVOT as below-

SELECT
SUBSTRING(ScoresOfAToC, (CHARINDEX(':',ScoresOfAToC, 0))+1,(CHARINDEX(',',ScoresOfAToC, 0))-((CHARINDEX(':',ScoresOfAToC, 0))+1)),
SUBSTRING(ScoresOfAToC, (CHARINDEX(':',ScoresOfAToC,(CHARINDEX(':',ScoresOfAToC,0))+1))+1,(CHARINDEX(',',ScoresOfAToC, (CHARINDEX(',',ScoresOfAToC, 0))+1))-((CHARINDEX(':',ScoresOfAToC,(CHARINDEX(':',ScoresOfAToC,0))+1))+1)),
SUBSTRING(ScoresOfAToC,CHARINDEX(':',ScoresOfAToC,CHARINDEX(':',ScoresOfAToC,(CHARINDEX(':',ScoresOfAToC,0))+1)+1)+1,LEN(ScoresOfAToC)),
SUBSTRING(ClassOfAToC, (CHARINDEX(':',ClassOfAToC, 0))+1,(CHARINDEX(',',ClassOfAToC, 0))-((CHARINDEX(':',ClassOfAToC, 0))+1)),
SUBSTRING(ClassOfAToC, (CHARINDEX(':',ClassOfAToC,(CHARINDEX(':',ClassOfAToC,0))+1))+1,(CHARINDEX(',',ClassOfAToC, (CHARINDEX(',',ClassOfAToC, 0))+1))-((CHARINDEX(':',ClassOfAToC,(CHARINDEX(':',ClassOfAToC,0))+1))+1)),
SUBSTRING(ClassOfAToC,CHARINDEX(':',ClassOfAToC,CHARINDEX(':',ClassOfAToC,(CHARINDEX(':',ClassOfAToC,0))+1)+1)+1,LEN(ScoresOfAToC)),
ScoresOfAToC,
ClassOfAToC
FROM
(
SELECT name,myFormat FROM table2
)AS A
PIVOT
(
    MAX(myFormat) FOR Name in ([ScoresOfAToC],[ClassOfAToC])
)PVT

it should form single row in o/p table as below 10 20 30 4 5 6 — krity, May 22 '19 at 12:58

How will I achieve the following requirement using sql function or query

1 Answers1