SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Rearrange data - from 1 field to X rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Tep
Starting Member

Netherlands
2 Posts

Posted - 03/25/2013 :  09:00:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 03/25/2013 :  09:55:42  Show Profile  Reply with Quote
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
)

Go to Top of Page

Tep
Starting Member

Netherlands
2 Posts

Posted - 03/25/2013 :  10:19:10  Show Profile  Reply with Quote
Great! Thank you very much, Bandi!

:-)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/25/2013 :  11:16:46  Show Profile  Reply with Quote
see xml based method here

http://visakhm.blogspot.in/2013/01/delimited-string-split-xml-parsing.html



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000