0

I need to split a comma-separated column into rows without affecting other columns.

My table ....

+-----+--------------+------------+-------------+
| id  | parent_title | account_id |    tags     |
+-----+--------------+------------+-------------+
| 647 | title999     |         64 | 361,381,388 |
| 646 | title998     |         64 | 361,376,388 |
+-----+--------------+------------+-------------+

Expected table......

+-----+--------------+------------+------+
| id  | parent_title | account_id | tags |
+-----+--------------+------------+------+
| 647 | title999     |         64 |  361 |
| 647 | title999     |         64 |  381 |
| 647 | title999     |         64 |  388 |
| 646 | title998     |         64 |  361 |
| 646 | title998     |         64 |  376 |
| 646 | title998     |         64 |  388 |
+-----+--------------+------------+------+
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rahul Hera
  • 61
  • 6
  • What SQL DB are you working on? Some DBs have a Split() function which does exactly what you are looking for. – Gil Adirim Jan 24 '15 at 07:09
  • 3
    String id's as comma separated values is very bas database design. You should normalize your tables. – Jens Jan 24 '15 at 07:16
  • I am using mssql DB, actually i'm using split with while loop to do this work, but time taken to exec is very high. – Rahul Hera Jan 24 '15 at 07:21

2 Answers2

1

Get yourself a good string splitting function. No database should be without one.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Then, do this (note this assumes you've built one of the string split functions and it's called SplitStrings_CLR())

;with t as
(
    select id = 647, parent_title = 'title999', account_id = 64, tags = '361,381,388' union all
    select id = 646, parent_title = 'title998', account_id = 64, tags = '361,381,388' 
)
select 
    id,
    parent_title,
    account_id,
    x.*
from t
cross apply sysmon.dbo.SplitStrings_CLR(tags, ',') x
Xedni
  • 3,662
  • 2
  • 16
  • 27
  • i never worked on CLR functions .. could you please refer any tutorial.. link you gave asking me to register . :( – Rahul Hera Jan 24 '15 at 07:29
  • Then don't build a CLR. Use one of the sql-based functions. The article I linked you is arguably the best article on the web regarding string splitting functions in SQL Server. If you need to build a split function, I'd recommend spending 20 seconds to register with the site. – Xedni Jan 24 '15 at 15:42
0

Use XML trick to do this.

SELECT id,
       parent_title,
       account_id,
       Split.a.value('.', 'Int') tags
FROM   (SELECT id,
               parent_title,
               account_id,
               Cast ('<M>' + Replace(tags, ',', '</M><M>') + '</M>' AS XML) AS Data
        FROM   [table]) AS A
       CROSS APPLY Data.nodes ('/M') AS Split(a)

SQLFIDDLE DEMO

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172