How to generate Multi-row for each row in a table using SQL only

Question

Possible Duplicate:
Split column to multiple rows

i have table contains the following data:-

Name
---------
mouse,keyboard,screen
harddisk,cable
processor

I need to create query to retrieve the data from this table to be as follow:-

Name
--------
mouse
keyboard
screen
harddisk
cable
processor

What RDBMS? Hopefully you want this query so you can normalize the database? — Martin Smith, Jan 13 '12 at 20:42

score 0 · Answer 1 · answered Jan 13 '12 at 20:46

0

First you need a split function like: http://blog.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx

Then you can run:

  select *
  from MyTable t
  cross apply dbo.Split(t.name) s

and s.items will contain what you want.

answered Jan 13 '12 at 20:46

John Gibb

10,603
2
37
48

Instead of custom split function you can use CTE for SQL Server 2005+. Look at http://stackoverflow.com/questions/8726111/sql-server-find-nth-occurrence-in-a-string – BartekR Jan 13 '12 at 20:51
True, but I think the loop might be more performant. Recursive CTEs have a limit to how many levels of recursion are supported etc. – John Gibb Jan 13 '12 at 20:53
thank you for your answer, but I need the solution in oracle not sql server – Islam Jan 13 '12 at 20:54
1

Duplicate: http://stackoverflow.com/questions/3713107/split-column-to-multiple-rows – BartekR Jan 13 '12 at 20:58

score 0 · Answer 2 · answered Jan 13 '12 at 20:51

Try this:

    DECLARE @t TABLE (Item VARCHAR(64))
    DECLARE @spot SMALLINT, @str VARCHAR(8000), @sql VARCHAR(8000), @cslist VARCHAR(8000)

    SELECT @cslist = mouse + ',' + keyboard,screen + ',' + harddisk + ',' + cable + ',' + processor
FROM Table

        WHILE @cslist <> ''  
        BEGIN  
            SET @spot = CHARINDEX(',', @cslist)

            IF @spot > 0  
                BEGIN  
                    SET @str = LEFT(@cslist, @spot - 1)  
                    SET @cslist = RIGHT(@cslist, LEN(@cslist) - @spot)  
                END  
            ELSE  
                BEGIN  
                    SET @str = @cslist  
                    SET @cslist = ''  
                END  
            INSERT @t SELECT @str

    SELECT * FROM @t

How to generate Multi-row for each row in a table using SQL only

2 Answers2