Creating a lookup table from a column in table

Question

I have a table in SQL Server that contains an ID and also a column with multiple values separated by a comma (like the example below)

ID        Category_number
-------------------------
1           3,5,6,8
2            4,8,23
3           4,7,5,3

I need to make this into a lookup table with 1 category number per row, so like below;

ID        Category_Number
-------------------------
1               3
1               5
1               6

I have been told that XPATH might be the solution to this. Does anyone have any sample code that will do this?

Thanks

Who told you this had anything to do with XPath? I guess you could use it, but I don't see any XML anywhere! — Francis Avila, Feb 13 '13 at 14:29
@FrancisAvila, I know XPath can be used as a hack to go from a normalized table to a comma-delimited string, although that's not its intended purpose. It's possible it could help you go the other way, although I'm not sure how you'd do it. — Jeff Rosenberg, Feb 13 '13 at 14:32

score 2 · Accepted Answer · edited May 23 '17 at 10:32

2

See this answer Split

Create that function in your database.

Then you can create the results you want using:

SELECT
    ID,
    A.S Category_Number
FROM
    MyCsvTable
    CROSS APPLY dbo.Split (',', MyCsvTable.Category_Number) A

edited May 23 '17 at 10:32

Community

1
1

answered Feb 13 '13 at 14:38

Tobsey

3,390
14
24

@user1016171 I noticed that and edited the answer. Is it correct now? – Tobsey Feb 13 '13 at 14:45

score 2 · Answer 2 · answered Feb 13 '13 at 14:46

I know you just accepted the solution, but assuming you're using SQL Server, here is an alternative approach without building a function:

 SELECT A.[id],  
         Split.a.value('.', 'VARCHAR(100)') AS Cat  
       FROM  
          (SELECT [id],  
             CAST ('<M>' + REPLACE(Cat, ',', '</M><M>') + '</M>' AS XML) AS String  
          FROM  YourTable
      ) AS A 
    CROSS APPLY String.nodes ('/M') AS Split(a)

And some Fiddle: http://sqlfiddle.com/#!3/cf427/3

Best of luck!

score 0 · Answer 3 · answered Feb 13 '13 at 15:58

You may also want to check HierarchyID in SQL SERVER. Some tutorial: http://www.codeproject.com/Articles/37171/HierarchyID-Data-Type-in-SQL-Server-2008

Basically all you need to do is to loop through string and find position of commas and replace them with chr(13) or such. In Oracle it can be done easily in a single query. I think the same can be done using HierarchyID in SQL Server starting from 2008 or maybe even earlier versions.

Creating a lookup table from a column in table

3 Answers3