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;
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -