Rearrange data - from 1 field to X rows

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)

Rearrange data - from 1 field to X rows

Author

Topic

Tep
Starting Member

2 Posts

Posted - 2013-03-25 : 09:00:15

Hi,

I am trying to change a table like this:

ID code
287 24;41
295 12;3;22
954 2;1

into this:

ID code
287 24
287 41
295 12
295 3
295 22
954 2
954 1

How should I do this? Maybe first I should pull apart the values from column code into more columns and thereafter something with a Pivot?

I hope someone can help me with this.

Thank you in advance,

Tep

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-25 : 09:55:42

[code]SELECT ID, s
FROM Yourtable
OUTER APPLY CustomSplit(';', Codes) -- Codes = Your column name in the Table

--[dbo].[CustomSplit] function
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CustomSplit] (@sep char(1), @s varchar(4000))
RETURNS table
AS
RETURN (
WITH Pieces(n, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT n + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT n,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS s
FROM Pieces
)

[/code]