Click here to Skip to main content
14,733,443 members
Please Sign up or sign in to vote.
4.00/5 (3 votes)
I want to create such function in SQL Server that takes following paramater
1. Data (Data in CSV format)
2. Delimiter (Delimiter used to separate data)
3. Number Of column (specify number of columns output table should have)

Value of parameter
Data - 1,2,3,4,5,6
Delimiter - ,(Comma)
Number of column

output for Number of column=2:
Col1Col2
12
34
56



output for Number of column=3:
Col1Col2Col3
123
456


Thanks in advance...! :)
Posted

This will convert your comma delimited string to one column, hope this will help you
ALTER FUNCTION [dbo].[split] (@p_ids VARCHAR(8000))
RETURNS @Ids TABLE (Ids BIGINT)
/*Returns a result set that contains all the Ids as row*/
AS
BEGIN
	IF @p_ids IS NULL
		BEGIN
			RETURN
		END
		
	DECLARE @id_str		VARCHAR(15)
	DECLARE @l_str		VARCHAR(8000)
	DECLARE @pos		INT
	SET @l_str = @p_ids
	SET @pos = CHARINDEX(',', @l_str)
	WHILE @pos > 0
		BEGIN
			SET @id_str = RTRIM(LTRIM(SUBSTRING(@l_str, 1, @pos-1)))
			INSERT INTO @Ids (Ids) VALUES (@id_str)
			SET @l_str = LTRIM(RTRIM(SUBSTRING(@l_str, @pos+1, 8000)))
			SET @pos = CHARINDEX(',', @l_str)
		END
	SET @id_str = LTRIM(RTRIM(@l_str))
	INSERT INTO @Ids (Ids) VALUES (@id_str)
	RETURN 
END
   
Comments
Dave Paras 22-Apr-11 7:19am
   
Output in Single column has been already achieved.
Can you please provide function that can convert at least for 2 columns.
Thanks for your time and answer.
[no name] 22-Apr-11 8:42am
   
you can do changes in this function and also get solution
Here you have an example of how to split a csv string into separate values. You can simply make a small change by adding an extra parameter to specify how many columns each row has and declare a counter variable to keep track of the current column number so you know when you have a complete row.
http://www.saqib-ansari.com/2010/04/split-csv-string-into-table-in-sql-server.html[^]

Good luck!
   
Comments
Dave Paras 22-Apr-11 7:44am
   
Yeah I know.
But main prob is that how to create dynamic columns.
Functionality provided in link has already been achieved.
Thanks for ans.
E.F. Nijboer 22-Apr-11 7:47am
   
Have a look at this link. I think you can find some good info there:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
Use the followinf logic to dynamiccaly add the column based on field value
I have used this logic for the same prolem....

DECLARE @Sql VARCHAR(8000), @Val VARCHAR(50)
SET @Sql = ''

DECLARE cur_AltTbl CURSOR FOR SELECT DISTINCT <<select field name to be converted as column>>FROM <<table name>>

OPEN cur_AltTbl
FETCH NEXT FROM cur_AltTbl INTO @Val

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Sql = 'ALTER TABLE <<Table on which synamic rows to be added>> ADD ' + @Val + ' INT'
PRINT @Sql
--INSERT INTO EmpDateShift
EXEC (@Sql)

FETCH NEXT FROM cur_AltTbl INTO @Val
END

CLOSE cur_AltTbl
DEALLOCATe cur_AltTbl
.........

Thanks
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900