t-sql - 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)

t-sql

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-05-18 : 12:04:12

Hello Everybody.

i have a data like below structure. I want to seperate Field2's data which is seperated by ; and have to count how many times repated on hole records, it could be multiple times in a single row. My output looks like below.(if we need update another table or create new field on same table i don't mind.)

declare @tbl1 table
(ID INT,
Field1 varchar(20),
Field2 varchar(10)
)

INSERT INTO @tbl1
SELECT 1, 'AAA;BBB;CCC',null

INSERT INTO @tbl1
SELECT 2,'BBB;CCC;DDD', null

INSERT INTO @tbl1
SELECT 3,'XYZ;MNOP;AAA', null

OUTPUT Like This :
1 AAA 2
2 BBB 3
3 CCC 3
4 DDD 3
5 MNOP 1
6 XYZ 1

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-18 : 12:58:22

I use this. dbo.fnCommaRemover is just a ParseValue function, which you can search this site for.
Jim

declare @tbl1 table
(ID INT,
Field1 varchar(20),
Field2 varchar(10)
)

INSERT INTO @tbl1
SELECT 1, 'AAA;BBB;CCC;XYZ;XYZ',null

INSERT INTO @tbl1
SELECT 2,'BBB;CCC;DDD', null

INSERT INTO @tbl1
SELECT 3,'AAA;XYZ;MNOP;AAA', null

SELECT Field1,COUNT(*)
FROM
(
select list as Field1
from @tbl1 t1
cross apply dbo.fnCommaRemover_R(field1,';') t2
) t3

GROUP BY Field1

Everyday I learn something that somebody else already knew

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-05-18 : 13:04:32

Jimf,
Could not find dbo.fnCommaRemover_R function. Would you provide link or function pls?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-18 : 13:07:13

fnCommaRemover is just a version of ParseValues function, which you can search this site for.

Jim

Everyday I learn something that somebody else already knew

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-05-18 : 13:21:54

Nop does not found.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-18 : 13:31:19

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104485

Everyday I learn something that somebody else already knew

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-05-20 : 14:38:07

I am trying to use visakh16's ParseValues function from above link.

How do i update t.InvoiceId into another new field call field3?

SELECT t.InvoiceId,b.Val FROM @tbl t
CROSS APPLY ParseValues(t.Dc)b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources