SQL - Break down a field's data into separate rows

Question

Given, for example, the following from table alpha:

Field1 | Field2 | Field3
------------------------
Foo    | Bar    | ABCD

How could I break this data down into:

Field1 | Field2 | Field3
------------------------
Foo    | Bar    | A
Foo    | Bar    | B
Foo    | Bar    | C
Foo    | Bar    | D

I'm sure there's a fancy join trick that could do it, but I can't figure it out. Speed optimisation isn't a priority - this query is only being used for a one-off report, so I don't mind if it's slow as molasses (gives me chance to make a coffee!)

is `field 3` delimeted by anything e.g. a comma or is just every character or ...? — T I, Jul 08 '13 at 09:20
Do you need to split the column3 by each character or is there something else? — Sachin Shanbhag, Jul 08 '13 at 09:20
possible duplicate of [SQL query to split column data into rows](http://stackoverflow.com/questions/13527537/sql-query-to-split-column-data-into-rows) — Sachin Shanbhag, Jul 08 '13 at 09:22
There's no delimeters, not fixed (but maximum of 6 characters), split by each character — Kai, Jul 08 '13 at 09:22
Possible Duplicate of [http://stackoverflow.com/questions/17518982/separate-the-data-in-mysql-using-comma-and-period#17518982](http://stackoverflow.com/questions/17518982/separate-the-data-in-mysql-using-comma-and-period#17518982) — Romesh, Jul 08 '13 at 09:26

score 1 · Answer 1 · answered Jul 08 '13 at 09:33

You can try following:

WITH CTE_LenF3 AS 
(
  -- find the length of each field3
  SELECT Field1, Field2, LEN(Field3) as Len_F3
  FROM alpha
)
,CTE_Numbers AS 
(
 --generate numbers from 1 to LEN(Filed3) for each field1,field2 combination
  SELECT Field1, Field2, 1 AS Nmb FROM CTE_LenF3
  UNION ALL
  SELECT c.Field1, c.Field2, Nmb + 1 FROM CTE_Numbers n
  INNER JOIN CTE_LenF3 c ON c.Field1 = n.Field1 AND c.Field2 = n.Field2
  WHERE Nmb + 1 <= LEN_F3
)
--join generated numbers with actual table and use substring to get the characters
SELECT a.Field1, a.Field2, SUBSTRING(a.Field3, n.Nmb, 1)
FROM CTE_Numbers n
INNER JOIN alpha a ON a.Field1 = n.Field1 AND a.Field2 = n.Field2
ORDER BY a.Field1, a.Field2, n.Nmb

SQLFiddle DEMO

score 1 · Accepted Answer · answered Jul 08 '13 at 09:34

you can do it by following steps easily :

Step1 : Create one sql table valued function which can split word in to characters . you can do it by run following script .

CREATE FUNCTION [dbo].[SPLITWORD](
@WORD VARCHAR(MAX) 
) RETURNS @words TABLE (item VARCHAR(8000))

 BEGIN
 declare @count int, @total int
 select @total = len(@WORD), @count = 0

 while @count <= @total
 begin
   insert into @words select substring(@WORD, @count, 1)
   select @count = @count + 1
 end

 RETURN
END

2.Steps Run following Query which will return result you want .

    SELECT A.FIELD1 , A.Field2 , B.ITEM
    FROM alpha AS A
    CROSS APPLY
    (
    SELECT * FROM SPLITWORD(A.Field3) WHERE ITEM != ''
    ) AS B

Damien_The_Unbeliever · Answer 3 · 2013-07-08T10:02:33.620

Something like:

declare @alpha table (Field1 varchar(20), Field2 varchar(20), Field3 varchar(6))
insert into @alpha(Field1, Field2, Field3) values
('Foo','Bar','ABCD')

;With Numbers(n) as (
    select 1 union all select 2 union all select 3 union all
    select 4 union all select 5 union all select 6
)
select Field1,Field2,SUBSTRING(Field3,n,1)
from
    @alpha
        inner join
    Numbers
        on
            n <= LEN(Field3)

(unless you already have a convenient Numbers table you haven't mentioned in the question, in which case its even simpler)

Result:

Field1               Field2               
-------------------- -------------------- ----
Foo                  Bar                  A
Foo                  Bar                  B
Foo                  Bar                  C
Foo                  Bar                  D

score 0 · Answer 4 · answered Jul 08 '13 at 09:35

DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='A,B,C,D,E'
SET @delimiter =','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)

replace your delimiter

score 0 · Answer 5 · answered Jul 08 '13 at 09:38

Any way the following query achieve your functionality

DECLARE @temp as table(newFiled3 varchar(1))
DECLARE @str_Value varchar(50),@count int,@i int=1
SET @str_Value=(SELECT Field3 FROM alpha)
SET @count=LEN(@str_Value)
WHILE(@i<=@count)
BEGIN
    INSERT INTO  @temp VALUES (SUBSTRING ( @str_Value ,@i , 1 ))
SET @i=@i+1
END


SELECT Field1,Field2,b.newFiled3 
FROM tblStudent a inner join @temp b ON a.Field1='Foo'

Any way looping is not a good method but still we need to go with that since your Filed3 ABCD is dynamic

SQL - Break down a field's data into separate rows

5 Answers5