CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Torpedo
Torpedo

Reputation: 181

How to split column values and add to a temp table

I am using SQL Server 2008 R2.

I have a table tblInstitution as follows

InstitutionCode   InstitutionDesc
---------------------------------
ABC               Abra Cada Brad   
DEF               Def Fede Eeee 
GHJ               Gee Hee

I want to split the values in InstitutionDesc and store it based on the institution code

InstitutionCode   Token   Score
-------------------------------
ABC               Abra      0
ABC               Cada      0
ABC               Brad      0
DEF               Def       0
DEF               Fede      0
DEF               Eeee      0
GHJ               Gee       0
GHJ               Hee       0

Is there a way I can do this in a set-based operation?

I have seen examples where a single column value can be split into multiple column values of the same row. But I am not able to find an example where the same column can be split into different rows. I am not sure what exactly should be searched for. Is it something to do with CTEs.

Upvotes: 0

Views: 613

Answers (1)

Eric J. Price
Eric J. Price

Reputation: 2785

Here is a recursive CTE option...

If      Object_ID('tempdb..#tblInstitution') Is Not Null Drop Table #tblInstitution;
Create  Table #tblInstitution (InstitutionCode Varchar(10), InstitutionDesc Varchar(50));

Insert  #tblInstitution (InstitutionCode, InstitutionDesc)
Values  ('ABC','Abra Cada Brad'),
        ('DEF','Def Fede Eeee'),
        ('GHJ','Gee Hee'),
        ('KLM','Kappa');

With    base As
(
        Select  InstitutionCode, 
                LTRIM(RTRIM(InstitutionDesc)) As InstitutionDesc
        From    #tblInstitution
),      recur As
(
        Select  InstitutionCode,
                Left(InstitutionDesc, CharIndex(' ', InstitutionDesc + ' ') - 1) As Token,
                Case
                When    CharIndex(' ', InstitutionDesc) > 0
                Then    Right(InstitutionDesc, Len(InstitutionDesc) - CharIndex(' ', InstitutionDesc))
                Else    Null
                End     As Remaining
        From    base
        Union   All
        Select  InstitutionCode,
                Left(Remaining, CharIndex(' ', Remaining + ' ') - 1) As Token,
                Case
                When    CharIndex(' ', Remaining) > 0
                Then    Right(Remaining, Len(Remaining) - CharIndex(' ', Remaining))
                Else    Null
                End     As Remaining
        From    recur
        Where   Remaining Is Not Null
)
Select  InstitutionCode,
        Token,
        0   As Score
From    recur
Order   By InstitutionCode

Upvotes: 1

Related Questions