sql-server-2008dbo

Split delimited string in one column and keep other column intact

My table data is as follows 

x------x--------------------x
| Key  |        Ids         |
x------x--------------------x
|  1   |    23,34,45,56,78  |
|  2   |    56,76,45,7,98   |
x------x--------------------x

I would like the output to be like

x---------------x
| Key  |   Ids  |
x------|--------x
|  1   |   23   |
|  1   |   34   |
|  1   |   45   |
|  1   |   56   |
|  1   |   78   | 
|  2   |   56   |
|  2   |   76   |
|  2   |   45   |
|  2   |   7    |
|  2   |   98   |
x------x--------x

I know to use dbo.split() but don't know to combine and show. Please advise

Solution

  • You can use CROSS APPLY to do this. Here's an example but you'll need to tweak the column names depending on your Split function:

    SELECT a.Key, b.Data from YourTable a
CROSS APPLY dbo.Split(a.Ids, ',') b