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
 General SQL Server Forums
 New to SQL Server Programming
 Split Function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sprotson
Yak Posting Veteran

75 Posts

Posted - 11/06/2012 :  08:05:52  Show Profile  Reply with Quote
Does anyone know of a function I can use that will split 1 column into multiple columns based on a delimiter (,)?

I also want to be able to name the column.

Data is currently in format 1,2,3,4,5,6 abd I want it to be

ColA ColB ColC ColD ColE ColF
1 2 3 4 5 6

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/06/2012 :  08:22:07  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Are there a fixed number of columns?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 11/06/2012 :  08:40:50  Show Profile  Reply with Quote
There will be a fixed number of 100 columns, but not all columns will necessarily have entries
Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 11/06/2012 :  11:11:24  Show Profile  Reply with Quote
I have found the following code that may work, but it is in MYSQL, does anyone know of a way I can convert the syntax to MSSQL?

CREATE FUNCTION SPLIT_STR(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, '');
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 11/07/2012 :  06:08:51  Show Profile  Reply with Quote
CREATE TABLE #test(id int, data varchar(100))

INSERT INTO #test VALUES (1,'This,is,a,test,string')
INSERT INTO #test VALUES (2,'See,if,it,can,be,split,into,many,columns') 

SELECT * FROM #test
DECLARE @pivot varchar(8000)
DECLARE @select varchar(8000)

SELECT 
        @pivot=coalesce(@pivot+',','')+'[col'+cast(number+1 as varchar(10))+']'
FROM 
        master..spt_values where type='p' and 
        number<=(SELECT max(len(data)-len(replace(data,',',''))) FROM #test)

SELECT 
    @select='
        select p.*
        from (
        select 
            id,substring(data, start+2, endPos-Start-2) as token,
            ''col''+cast(row_number() over(partition by id order by start) as varchar(10)) as n
        from (
            select 
                id, data, n as start, charindex('','',data,n+2) endPos
                from (select number as n from master..spt_values where type=''p'') num
                cross join 
                (
                    select 
                        id, '','' + data +'','' as data 
                    from 
                        #test
                ) m
            where n < len(data)-1
            and substring(data,n+1,1) = '','') as data
        ) pvt
        Pivot ( max(token)for n in ('+@pivot+'))p'

EXEC(@select)

DROP TABLE #test


--
Chandu

Edited by - bandi on 11/07/2012 06:23:27
Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 11/07/2012 :  06:22:50  Show Profile  Reply with Quote
quote:
Originally posted by bandi

CREATE TABLE #test(id int, data varchar(100))

INSERT INTO #test VALUES (1,'This,is,a,test,string')
INSERT INTO #test VALUES (2,'See,if,it,can,be,split,into,many,columns')

SELECT * FROM #test
DECLARE @pivot varchar(8000)
DECLARE @select varchar(8000)

SELECT
@pivot=coalesce(@pivot+',','')+'[col'+cast(number+1 as varchar(10))+']'
FROM
master..spt_values where type='p' and
number<=(SELECT max(len(data)-len(replace(data,',',''))) FROM #test)

SELECT
@select='
select p.*
from (
select
id,substring(data, start+2, endPos-Start-2) as token,
''col''+cast(row_number() over(partition by id order by start) as varchar(10)) as n
from (
select
id, data, n as start, charindex('','',data,n+2) endPos
from (select number as n from master..spt_values where type=''p'') num
cross join
(
select
id, '','' + data +'','' as data
from
#test
) m
where n < len(data)-1
and substring(data,n+1,1) = '','') as data
) pvt
Pivot ( max(token)for n in ('+@pivot+'))p'

EXEC(@select)

DROP TABLE #test


--
Chandu



Sorry to be a pain, but how can I use that code against an existing view?

It is named HostApplication and has two columns Host and Application, with application being the string of application seperated by a comma.

So in your example id would need to be the host.

I also need to use this in a report, how would I do that.

Sorry - I am very much an SQL novice with basic experience of creating views and usimg in reports when some experience of stored procedures purely to set report parameters.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/07/2012 :  07:01:27  Show Profile  Reply with Quote
Borrowing Chandu's sample data, this is another approach. To use this, you will need to first install the string split function from this article. The function is in Fig. 21. Copy and paste that to an SSMS window and run it to install the function. Then, the code will be like this:
CREATE TABLE #test(id int, data varchar(100))

INSERT INTO #test VALUES (1,'This,is,a,test,string')
INSERT INTO #test VALUES (2,'See,if,it,can,be,split,into,many,columns') 

SELECT * FROM #test

SELECT * FROM
(
	SELECT
		Id, ItemNumber, Item
	FROM
		#test
		CROSS APPLY  dbo.DelimitedSplit8K([data],',') dsk
)s PIVOT
(MAX (Item) FOR ItemNumber IN  
	([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
	[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
	[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
	[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],
	[41],[42],[43],[44],[45],[46],[47],[48],[49],[50]
))P

DROP TABLE #test;


To use it with your view, change it as shown below - it is the same as the code above, except, I have replaced the test table name and column names with your table name and column names:
SELECT * FROM
(
	SELECT
		Host, ItemNumber, Item
	FROM
		HostApplication
		CROSS APPLY  dbo.DelimitedSplit8K([Application],',') dsk
)s PIVOT
(MAX (Item) FOR ItemNumber IN  
	([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
	[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
	[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
	[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],
	[41],[42],[43],[44],[45],[46],[47],[48],[49],[50]
))P
Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 11/07/2012 :  07:29:50  Show Profile  Reply with Quote
quote:
Originally posted by sunitabeck

Borrowing Chandu's sample data, this is another approach. To use this, you will need to first install the string split function from this article. The function is in Fig. 21. Copy and paste that to an SSMS window and run it to install the function. Then, the code will be like this:
CREATE TABLE #test(id int, data varchar(100))

INSERT INTO #test VALUES (1,'This,is,a,test,string')
INSERT INTO #test VALUES (2,'See,if,it,can,be,split,into,many,columns') 

SELECT * FROM #test

SELECT * FROM
(
	SELECT
		Id, ItemNumber, Item
	FROM
		#test
		CROSS APPLY  dbo.DelimitedSplit8K([data],',') dsk
)s PIVOT
(MAX (Item) FOR ItemNumber IN  
	([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
	[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
	[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
	[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],
	[41],[42],[43],[44],[45],[46],[47],[48],[49],[50]
))P

DROP TABLE #test;


To use it with your view, change it as shown below - it is the same as the code above, except, I have replaced the test table name and column names with your table name and column names:
SELECT * FROM
(
	SELECT
		Host, ItemNumber, Item
	FROM
		HostApplication
		CROSS APPLY  dbo.DelimitedSplit8K([Application],',') dsk
)s PIVOT
(MAX (Item) FOR ItemNumber IN  
	([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
	[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
	[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
	[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],
	[41],[42],[43],[44],[45],[46],[47],[48],[49],[50]
))P




Whete is the code for the function, you mention figure 21 in an article, whihc article is that?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/07/2012 :  07:39:39  Show Profile  Reply with Quote
Oops, forgot to include the link: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 11/07/2012 :  08:04:13  Show Profile  Reply with Quote
quote:
Originally posted by sunitabeck

Oops, forgot to include the link: http://www.sqlservercentral.com/articles/Tally+Table/72993/




Thanks, that worked a treat and is exactly what I was looking for.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/07/2012 :  11:49:01  Show Profile  Reply with Quote
Great! Glad to be of help.
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.09 seconds. Powered By: Snitz Forums 2000