split the 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)

split the records

Author

Topic

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-08-25 : 17:17:31

Hi All,

I need the query for the beneath.

One of the field in my table is as follows.

List
1-3,3-5,2-1,
1-2,
1-22,3-90,
2-33,2-23,4-90,4-80

Here for eg 1-3 is a set and 3-5 is a set and 2-1 is also a set.

My requirement is

In 1-2 ---> 1 is a id referred to another table and 2 is price.

I want to display the beneath output for 1-2.

persontype price

aaa 2------------------->1 is aaa and 2 is price.

Kindly help me.

kamal.

markross
Starting Member

4 Posts

Posted - 2008-08-25 : 17:43:57

Are you saying that:
1-3,3-5,2-1,
1-2,
1-22,3-90,
2-33,2-23,4-90,4-80
is 4 rows from one column which has pairs that repersent (tbl2ID,PriceCode)?

If so, I think that the table needs to be redesigned with a another detail table

OtherWise, I would use a stroreeProcedure to write a bit of code to loop throught the sets

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-25 : 23:21:03

[code]DECLARE @sample TABLE
(
List varchar(20)
)
INSERT INTO @sample
SELECT '1-3,3-5,2-1' UNION ALL
SELECT '1-2' UNION ALL
SELECT '1-22,3-90' UNION ALL
SELECT '2-33,2-23,4-90,4-80'

DECLARE @another TABLE
(
ID varchar(10),
persontype varchar(10)
)

INSERT INTO @another
SELECT '1', 'aaa' UNION ALL
SELECT '2', 'bbb'

SELECT s.List, s.stringval, a.persontype, s.price
FROM
(
SELECT s.List, c.stringval,
ID = CASE WHEN CHARINDEX('-', c.stringval) <> 0 THEN left(c.stringval, CHARINDEX('-', c.stringval) - 1) END,
price = CASE WHEN CHARINDEX('-', c.stringval) <> 0 THEN right(c.stringval, LEN(c.stringval) - CHARINDEX('-', c.stringval)) END
FROM @sample s
CROSS apply CSVTable(s.List) c
) s
INNER JOIN @another a ON s.ID = a.ID

/*
List stringval persontype price
-------------------- ----------- ---------- ------
1-3,3-5,2-1 1-3 aaa 3
1-3,3-5,2-1 2-1 bbb 1
1-2 1-2 aaa 2
1-22,3-90 1-22 aaa 22
2-33,2-23,4-90,4-80 2-33 bbb 33
2-33,2-23,4-90,4-80 2-23 bbb 23

(6 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-08-26 : 09:51:05

Hi khtan

I used your code but i met the following error.
"Incorrect syntax near 'apply'." I have sql server 2005 in my system.

I need strored procedure or a query. Kinldy help me.

kamal........

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-26 : 09:56:30

make sure the compatibility level is set at 90

KH
[spoiler]Time is always against us[/spoiler]

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-08-26 : 10:05:22

Thanks

I have change the compatibility level 90.

But now I faced the following error message.

"Invalid object name 'CSVTable'."

What is 'CSVTable'?

kamal..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-26 : 10:11:10

you can get it from here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-26 : 10:12:06

or you can use similar functions like fnParseList
from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources