Split one column to multiple ones

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 2012 Forums

Transact-SQL (2012)

Split one column to multiple ones

Author

Topic

Mikehjun
Starting Member

24 Posts

Posted - 2013-09-30 : 12:20:44

One column has value like this;(numbers are sperated by bar character)
10|20|40|20|10
5|10|20|30|35

I'd like to make multiple columns like this

Col1, Col2, Col3, Col4, Col5
10, 20, 40, 20, 10
5, 10, 20, 30, 35

Please help!!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-30 : 12:54:06

Here is an example of how you can do this. The delimitedSplit8K function that I am usin is available in Jeff Moden's article here http://www.sqlservercentral.com/articles/Tally+Table/72993/

CREATE TABLE #tmp(col1 VARCHAR(32));
INSERT INTO #tmp VALUES ('10|20|40|20|10'),('5|10|20|30|35');

SELECT
	*
FROM
	#tmp t
	CROSS APPLY dbo.delimitedSplit8K(col1,'|')
PIVOT(MAX(Item) FOR ItemNumber in ([1],[2],[3],[4],[5]) )P

DROP TABLE #tmp;

Mikehjun
Starting Member

24 Posts

Posted - 2013-09-30 : 13:07:36

When I use string for value, it works great but when I use number, it returns null.

DECLARE @T TABLE (Filename VARCHAR(100))

INSERT @T (Filename)
VALUES
('10|20|30|40|50'),
('13|23|33|3|65'),
('31|4|5|6|85')

SELECT
PARSENAME(REPLACE(Filename, '|', '.'), 5) AS COL1,
PARSENAME(REPLACE(Filename, '|', '.'), 4) AS COL2,
PARSENAME(REPLACE(Filename, '|', '.'), 3) AS COL3,
PARSENAME(REPLACE(Filename, '|', '.'), 2) AS COL4,
PARSENAME(REPLACE(Filename, '|', '.'), 1) AS COL5
FROM @T

DECLARE @T TABLE (Filename VARCHAR(100))

INSERT @T (Filename)
VALUES
('Donald|Duck|suck'),
('Mikey|Mouse|suck')

SELECT
PARSENAME(REPLACE(Filename, '|', '.'), 3) AS COL1,
PARSENAME(REPLACE(Filename, '|', '.'), 2) AS COL2,
PARSENAME(REPLACE(Filename, '|', '.'), 1) AS COL3
FROM @T

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-09-30 : 13:21:18

PARSENAME returns null if there are more than 4 parsable elements:

SELECT PARSENAME('4.3.2.1',1), PARSENAME('5.4.3.2.1',1)

You'll need to use the function James posted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources