split number into new row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

split number into new row

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2008-06-04 : 15:06:35

I have the following:

id docid groupid Option
48 9861 1 1235
35 3973 3 34
23 14820 1 12346

would like to see this result:

id docid groupid Option
48 9861 1 1
48 9861 1 2
48 9861 1 3
48 9861 1 5
35 3973 3 3
35 3973 3 4
23 14820 1 1
23 14820 1 2
23 14820 1 3
23 14820 1 4
23 14820 1 6

thanks you

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-06-04 : 15:11:31

Here's one way (assuming Option is varchar)


declare @t table (id int, docid int, groupid int, [Option] varchar(10))
insert @t
select 48, 9861, 1, '1235' union all
select 35, 3973, 3, '34' union all
select 23, 14820, 1, '12346'

select t.id, t.docid, t.groupid, substring(t.[option], v.number, 1) [Option]
from @t t
cross join master..spt_values v
where  v.type = 'p'
and    v.number > 0
and    v.number <= len(t.[option])

output:
id          docid       groupid     Option
----------- ----------- ----------- ------
48          9861        1           1
48          9861        1           2
48          9861        1           3
48          9861        1           5
35          3973        3           3
35          3973        3           4
23          14820       1           1
23          14820       1           2
23          14820       1           3
23          14820       1           4
23          14820       1           6

Be One with the Optimizer
TG

hai
Yak Posting Veteran

84 Posts

Posted - 2008-06-04 : 15:54:25

thanks....

raky
Aged Yak Warrior

767 Posts

Posted - 2008-06-05 : 09:08:32

try this one also

DECLARE @TAB table
(
id INT,
docid INT,
groupid INT,
op VARCHAR(10)
)
INSERT INTO @tab
SELECT 49,2322,1,'1234' UNION all
SELECT 50,3234,2,'34' UNION all
SELECT 51,3235,3,'12'

SELECT id,docid,groupid, (SELECT(SUBSTRING(op,s.number+1,1))) AS op
FROM @tab t
INNER JOIN
master..spt_values s ON S.TYPE = 'P'
where (SUBSTRING(op,s.number+1,1)) != ''

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-05 : 09:42:27

Why not this?

DECLARE	@Sample TABLE (id INT, docid INT, groupid INT, [Option] VARCHAR(10))

INSERT	@Sample
SELECT	48, 9861, 1, '1235' UNION ALL
SELECT	35, 3973, 3, '34' UNION ALL
SELECT	23, 14820, 1, '12346'

;WITH Yak (id, docid, groupid, [option], pos, chr)
AS (
	SELECT	id,
		docid,
		groupid,
		[option],
		1,
		LEFT([option], 1)
	FROM	@Sample

	UNION ALL

	SELECT	id,
		docid,
		groupid,
		[option],
		Pos + 1,
		SUBSTRING([option], Pos + 1, 1)
	FROM	Yak
	WHERE	Pos < LEN([option])
)

SELECT		id,
		docid,
		groupid,
		chr
FROM		Yak
ORDER BY	id,
		pos

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-05 : 09:57:33

Or this

-- Peso 2
SELECT		p.id,
		p.docid,
		p.groupid,
		p.[option]
FROM		(
			SELECT	id,
				docid,
				groupid,
				NULLIF(SUBSTRING([option], 1, 1), '') AS Col1,
				NULLIF(SUBSTRING([option], 2, 1), '') AS Col2,
				NULLIF(SUBSTRING([option], 3, 1), '') AS Col3,
				NULLIF(SUBSTRING([option], 4, 1), '') AS Col4,
				NULLIF(SUBSTRING([option], 5, 1), '') AS Col5,
				NULLIF(SUBSTRING([option], 6, 1), '') AS Col6,
				NULLIF(SUBSTRING([option], 7, 1), '') AS Col7,
				NULLIF(SUBSTRING([option], 8, 1), '') AS Col8,
				NULLIF(SUBSTRING([option], 9, 1), '') AS Col9,
				NULLIF(SUBSTRING([option], 10, 1), '') AS Col10
			FROM	@Sample
		) AS s
UNPIVOT		(
			[option]
			FOR theCol IN (s.Col1, s.Col2, s.Col3, s.Col4, s.Col5, s.Col6, s.Col7, s.Col8, s.Col9, s.Col10)
		) AS p

Results:


        RowCounts  CPU     Duration  Reads      Writes
        ---------  ------  --------  ---------  ------
TG        655,360   1,048   19,349     196,833	     0
Raky      655,360  64,344  138,753     817,747	     0
Peso 1    655,360  11,657   36,725   6,845,649	    62
Peso 2    655,360     672   17,971         224	     0

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources