how to separate records - SQL Server Forums

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 separate records

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-03-05 : 11:13:46

Hello everyone,

can someone advise to separate the data into another temp table? i have records look like below, i need the separate col1 value into another temp table, see the output looks like.

Declare @tbl1 table
(ID INT,
Col1 varchar(50),
Col2 varchar(50)
)

INSERT INTO @tbl1
SELECT 1,'A1;B1;C1;D1',NULL

INSERT INTO @tbl1
SELECT 2,'X1;X2',NULL

INSERT INTO @tbl1
SELECT 3,'M1',NULL

INSERT INTO @tbl1
SELECT 4,'N1;N2;N3',NULL

select * from @tbl1

OUTPUT :
ID Col1

1 A1
1 B1
1 C1
1 D1
2 X1
2 X2
3 M1
4 N1
4 N2
4 N3

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-03-05 : 12:32:28

Anyone has an idea?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-05 : 13:05:20

You can use a string splitter function - there are several available on this forum, for example: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

These links also may be useful (Fig 21):
http://www.sqlservercentral.com/articles/Tally+Table/72993/

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-03-05 : 13:17:05

How do i pass table name here : @str
i have to pass values (col1) from @tbl1 table

-- Create temp table to test inserting values into
create table #t (num int)

-- Create a comma delimited string to test with
declare @str varchar(500)
select @str = '4,2,7,7834,45,24,45,77'

--------------------------------------------------------
---- Code to load the delimited string into a table ----
--------------------------------------------------------

-- Create insert for comma delimited values
declare @sql varchar(8000)
select @sql = 'insert into #t select '+
replace(@str,',',' union all select ')

-- Load values from comma delimited string into a table
exec ( @SQL )

--------------------------------------------------------
--------------------------------------------------------

-- Select values from temp table to show results
select * from #t

if object_id('tempdb..#t') is not null
begin drop table #t end

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-05 : 13:29:40

Personally, I would use a table function



CREATE FUNCTION [dbo].[udf_Table](@ParmList varchar(8000), @Delim varchar(20))
RETURNS @table TABLE 
(Parameter varchar(255))

AS 

/*
 SELECT * FROM dbo.udf_Table( 'a|~|b|~|c', '|~|')
*/

BEGIN
	DECLARE @x int, @Parameter varchar(255)

	WHILE CHARINDEX(@Delim, @ParmList)-1 > 0
	  BEGIN 
		INSERT INTO @table(Parameter) 
		SELECT SUBSTRING(@ParmList,1,CHARINDEX(@Delim, @ParmList)-1)
		

		SELECT @ParmList  = SUBSTRING(@ParmList,CHARINDEX(@Delim, @ParmList)+LEN(@Delim), LEN(@ParmList)-CHARINDEX(@Delim,@ParmList))
	  END 
	INSERT INTO @table(Parameter) SELECT @ParmList
	RETURN
END

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-03-05 : 13:45:09

how do we pass the table name instate of values.

SELECT * FROM dbo.udf_Table( 'a|~|b|~|c', '|~|')

select * from dbo.udf_table (@tbl1) ??

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-05 : 13:49:42

Try this

SELECT ID, Parameter FROM @tbl1 CROSS JOIN dbo.udf_Table( Col1, ',')
ORDER BY ID, Parameter

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-03-05 : 14:01:33

i got invalid column name

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-03-05 : 14:37:26

any update??

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-05 : 14:45:48

yeah...it doesn't work the way I though it would...hold on

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-05 : 15:55:36

use CROSS APPLY, not CROSS JOIN

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-05 : 16:22:41

...and there it is..Thanks Gail



Declare @tbl1 table 
(ID INT,
Col1 varchar(50),
Col2 varchar(50)
)

INSERT INTO @tbl1 (ID, Col1, Col2)
SELECT 1,'A1;B1;C1;D1',NULL UNION ALL
SELECT 2,'X1;X2',NULL UNION ALL
SELECT 3,'M1',NULL UNION ALL
SELECT 4,'N1;N2;N3',NULL

SELECT ID, Parameter FROM @tbl1 CROSS APPLY dbo.udf_Table( Col1, ';')
ORDER BY ID, Parameter

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-05 : 16:26:43

So CROSS APPLY Itself is a table valued function???

BOL

quote:

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

Note:
To use APPLY, the database compatibility level must be at least 90.

There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-05 : 17:16:45

No, cross apply is a join operator, just like inner join, outer join, etc.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources