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 2012 Forums
 Transact-SQL (2012)
 Split one column to multiple ones
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mikehjun
Starting Member

24 Posts

Posted - 09/30/2013 :  12:20:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 09/30/2013 :  12:54:06  Show Profile  Reply with Quote
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 - 09/30/2013 :  13:07:36  Show Profile  Reply with Quote
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

USA
15667 Posts

Posted - 09/30/2013 :  13:21:18  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
  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.03 seconds. Powered By: Snitz Forums 2000