Delimited Dilemma - 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 2000 Forums

Import/Export (DTS) and Replication (2000)

Delimited Dilemma

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-10 : 07:58:36

Zac writes "I could really use your assistance.

The objective is match Table A, Column "Equipment", a comma delimited series of codesets to Table B, Column "Equipment_ID"
and Column "Equip_Desc" and then to concantenate "Equip_Desc" back into a delimited string and update the original field from whence they came.

Example Column "Equipment", Field now reads
"Ceiling Fan, Hot Tub, Sauna, Fiber Wired Home Network :-)" in stead of "2,5,1,9".

Table A, Column "Equipment", Field "2,5,1,9"
Table B, Column "Equipmnet_ID, Field "2", Column "Equip_Desc" Field "Ceiling Fan"
Table B, Column "Equipmnet_ID, Field "5", Column "Equip_Desc" Field "Hot Tub"
Table B, Column "Equipmnet_ID, Field "1", Column "Equip_Desc" Field "Sauna"
Table B, Column "Equipmnet_ID, Field "9", Column "Equip_Desc" Field "Fiber Wired Network :-)""

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-10 : 08:55:13

[code]create table tableA (a_id int identity(0,1), equipment varchar(8000) )

insert into tableA
select '2,5,1,9' union all
select '1,9'

create table tableB (Equipment_ID int, equip_desc varchar(100) )

insert into tableB
select 2,'Ceiling Fan' union all
select 5,'Hot Tub' union all
select 1, 'Sauna' union all
select 9,'Fiber Wired Network :-)'

select * from tableA
select * from tableB

--Parsing CSV Values Into Multiple Rows
--http://www.sqlteam.com/item.asp?ItemID=2652

select identity(int,0,1) id, d.a_id,d.equipment_id ,b.equip_desc, replicate(' ',8000) as csv_equip_desc
into #tableC
from
(
SELECT a_id,
NullIf(SubString(',' + equipment + ',' , n , CharIndex(',' , ',' + equipment + ',' , n) - n) , '') equipment_id
FROM numbers, tableA
WHERE n <= Len(',' + equipment + ',') AND SubString(',' + equipment + ',' , n - 1, 1) = ','
AND CharIndex(',' , ',' + equipment + ',' , n) - n > 0 --remove this line to keep NULL rows
) d
join tableB b on b.equipment_id = d.equipment_id

--Converting Multiple Rows into a CSV String (Set Based Method)
--http://www.sqlteam.com/item.asp?ItemID=11021

declare
@equip_desc varchar(8000),
@last_id int,
@equipment_id int

select
@equip_desc = '',
@equipment_id = 0,
@last_id = -1

--here is the meat of the work
update
#tableC
set
@equip_desc = csv_equip_desc = case
when @last_id <> a_id then equip_desc
else @equip_desc + ', ' + equip_desc
end,
@last_id = a_id

--select * from #tableC

--Update tableA
update A
set equipment = csv_equip_desc
from
(
select a_id,max(csv_equip_desc) csv_equip_desc from #tableC
group by a_id
) d
join tableA A on a.a_id = d.a_id

--Result
select * from tableA

drop table tableA, tableB, #tableC
[/code]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-10 : 09:30:53

by the way -- you have just learned a good lesson on why NOT to store data that way ! store it in a normalized fashion, in multiple rows. it is much easier to *present* the results in a CSV from data in rows, rather than to *store* data in CSV's and then to have to extract it any time you wish to perform SQL.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources