How to split the concatenated value into columns

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

Transact-SQL (2005)

How to split the concatenated value into columns

Author

Topic

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-12-24 : 01:46:55

Hi,

i would like to split the concatenated values into columns.

eg: i have a table with values like below:

Table1:
Column1...column2
ABC....100,200,300,
XYZ....200,300,
DDT....100,
LMN....200,

and the expected output is

Table1:
Column1...column2
ABC....100,
ABC....200,
ABC....300,
XYZ....200,
XYZ....300,
DDT....100,
LMN....200,

Please assist me.

Rgds.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2009-12-24 : 01:55:57

How about something like...

--function for parsing
	CREATE FUNCTION dbo.fnParseCSV (@String VARCHAR(8000), @Delimeter CHAR(1)) 
	RETURNS TABLE 
	AS
	RETURN 
	(
		SELECT 
			LTRIM(RTRIM(NULLIF(SUBSTRING(@Delimeter + @String + @Delimeter, Number, CHARINDEX(@Delimeter, @Delimeter + @String + @Delimeter, Number) - Number), ''))) AS value
		FROM 
			master..spt_values 
		WHERE 
			Type = 'P' 
				AND Number <= LEN(@Delimeter + @String + @Delimeter)
				AND SUBSTRING(@Delimeter + @String + @Delimeter, Number - 1, 1) = @Delimeter
				AND CHARINDEX(@Delimeter, @Delimeter + @String + @Delimeter, Number) - Number > 0
	)
	GO

--solution

	SELECT
		t1.column1, 
		f.value + ',' AS column2
	FROM
		Table1 t1
		CROSS APPLY dbo.fnParseCSV(t1.column2,',') f
	ORDER BY
		t1.column1

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-12-24 : 02:15:40

is there any solution using direct query? without function.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2009-12-24 : 02:25:27

quote:
Originally posted by ganny

is there any solution using direct query? without function.

Sure...

	SELECT
		t1.column1, 
		f.value + ',' AS column2
	FROM
		Table1 t1
		CROSS APPLY 
		(
			SELECT 
				LTRIM(RTRIM(NULLIF(SUBSTRING(',' + t1.column2 + ',', Number, CHARINDEX(',', ',' + t1.column2 + ',', Number) - Number), ''))) AS value
			FROM 
				master..spt_values 
			WHERE 
				Type = 'P' 
					AND Number <= LEN(',' + t1.column2 + ',')
					AND SUBSTRING(',' + t1.column2 + ',', Number - 1, 1) = ','
					AND CHARINDEX(',', ',' + t1.column2 + ',', Number) - Number > 0
		)f	
	ORDER BY
		t1.column1

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-12-24 : 02:46:58

Hi,

the query is not clear also it is showing in the subquery for cross apply "Number".

can you pls advise, or pls advise the query based on the table i gave for better understanding.

Thank you.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2009-12-24 : 02:59:08

The inner query parses the concatenated fields. It uses a "Tally" table technique. Much has been written on this here if you search for it.

The "Number" column comes from spt_values - it is a substitute for a "Tally" table.


	SELECT
		number
	FROM
		master..spt_values
	WHERE
		Type = 'P'

The "Number" is used to move through the string (i.e. "100,200,300,") and look for the specified delimiter ',' and parse out the value located between the delimiter.

DECLARE @Delimeter varchar(20) = ','
DECLARE @string varchar(20) = '100,200,300,'

		SELECT 
			LTRIM(RTRIM(NULLIF(SUBSTRING(@Delimeter + @String + @Delimeter, Number, CHARINDEX(@Delimeter, @Delimeter + @String + @Delimeter, Number) - Number), ''))) AS value
		FROM 
			master..spt_values 
		WHERE 
			Type = 'P' 
				AND Number <= LEN(@Delimeter + @String + @Delimeter)
				AND SUBSTRING(@Delimeter + @String + @Delimeter, Number - 1, 1) = @Delimeter
				AND CHARINDEX(@Delimeter, @Delimeter + @String + @Delimeter, Number) - Number > 0

We instruct this inner query to perform this function for each row in Table1.
The CROSS APPLY operator allows us to pass in a value from the outer table, in this case Table1.column1 and create a corresponding record for each of the parsed values.

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-12-24 : 03:40:22

Dear Sir,

Thank you for the query. It works great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources