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

Question

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            |
-------------------------------------------

Which SQL Server version are you using? Based on that, you might get a different answer. — Evaldas Buinauskas, Sep 07 '18 at 13:15
Possible duplicate of [join comma delimited data column](https://stackoverflow.com/questions/16507239/join-comma-delimited-data-column) — philipxy, Sep 08 '18 at 05:21
Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using one variant search for your title & keywords for your tags. See the downvote arrow mouseover text. — philipxy, Sep 08 '18 at 05:22

score 1 · Accepted Answer · answered Sep 07 '18 at 13:30

1

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

answered Sep 07 '18 at 13:30

John Cappelletti

79,615
7
44
66

1

Cheeky solution there, creating an XML doc on the fly. I haven't seen that one before. +1 for adding an arrow to my quiver. – Eric Brandt Sep 07 '18 at 13:35
@EricBrandt I was slow to adopt XML. I was foolish. You'd be surprised at the possibilities. – John Cappelletti Sep 07 '18 at 13:38
For the record, I'm still in the "yeah, maybe I should start taking it seriously" phase myself. – Eric Brandt Sep 07 '18 at 13:50

score 0 · Answer 2 · answered Sep 07 '18 at 13:17

0

Taking string split function from this answer: T-SQL split string

You could run your query as following:

SELECT T.Name, X.*
FROM TABLE AS T
CROSS APPLY dbo.splitstring(Codes) AS X;

answered Sep 07 '18 at 13:17

Evaldas Buinauskas

13,739
11
55
107

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

2 Answers2