Search code examples
sql-serversql-server-2012database-normalization

SQL Server - write query that normalizes comma separated values in a poorly constructed schema

Problem: I am querying a poorly normalized database (I have no say in the design of the data and cannot change it)

Given the following database:

------------------------------------------
|      Name      |         Codes          |
------------------------------------------
|       Josh     |    A2A-8292            |
-------------------------------------------
|       Adam     |    D2C-1292, B2A-7292  |
-------------------------------------------
|       Jery     |    A2A-1292, F2A-2292, | 
|                |    C2A-2292            |
-------------------------------------------

How can I write a query that returns the normalized version e.g:

 ------------------------------------------
|      Name      |         Codes          |
------------------------------------------
|       Josh     |    A2A-8292            |
-------------------------------------------
|       Adam     |    D2C-1292            |
-------------------------------------------
|       Adam     |    B2A-7292            |
-------------------------------------------
|       Jery     |    A2A-1292            |
-------------------------------------------
|       Jery     |    F2A-2292            |
-------------------------------------------
|       Jery     |    C2A-2292            |
-------------------------------------------
Solution

  • If you can't use a TVF, here's another option

    Example

    Select A.Name 
      ,B.*
 From  YourTable A
 Cross Apply (
                Select RetSeq = row_number() over (order by (Select null))
                      ,RetVal = ltrim(rtrim(B2.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace(Codes,',','</x><x>')+'</x>' as xml).query('.')) as B1
                Cross Apply x.nodes('x') AS B2(i)
             ) B

    Returns

    Name    RetSeq  RetVal
Josh    1       A2A-8292
Adam    1       D2C-1292
Adam    2       B2A-7292
Jery    1       A2A-1292
Jery    2       F2A-2292
Jery    3       C2A-2292