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
 Transact-SQL (2000)
 Combining multiple rows into one

Author  Topic 

bopritchard
Starting Member

3 Posts

Posted - 2010-09-23 : 11:29:02
code description
1500 Hat Tie Shoes Boots
1500 Tie Blue Red Cat
1500 Shoes Red Fish Cup
1650 Hello Seed Tree
1650 Seed Pencil Paper
1650 Knife Phone Tree

I need a single row for each code with a description of each unique word in the description column. there are no set number of rows per code. SQLServer 2000

Really appreciate help on this one.

So my results should look like

1500 Hat Tie Shoes Boots Blue Red Cat Fish Cup
1650 Hello Seed Tree Pencil Paper Knife Phone

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-23 : 11:59:56
This would be sooooooo much easier on 2005 or better?

Can you upgrade.

I'm puzzling out a 2000 solution but it'll be messy.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-23 : 12:08:54
Here's a way. It's horrible. Would be both much shorter and much nicer on 2005. Wouldn't have a cursor......

It would be better

Note -- I've changed the code column to cDef so that it doesn't get in the way of the code tags......
Written on a database in compatibility 80 (2000) However NOT ON A 2000 Server. Not sure if it will all work on 2000 db

DROP FUNCTION CG_PARSE_ARRAY
GO

CREATE FUNCTION CG_PARSE_ARRAY (
@array VARCHAR(8000)
, @separator CHAR(1) = ' '
)
RETURNS @return TABLE (
[ID] INT IDENTITY(1,1) PRIMARY KEY
, [value] VARCHAR(8000)
)
AS BEGIN

-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma

DECLARE @separator_position INT -- This is used to locate each separator character
DECLARE @array_value VARCHAR(8000) -- this holds each array value as it is returned

-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
SET @array = @array + @separator

-- Loop through the string searching for separtor characters
WHILE ( PATINDEX('%' + @separator + '%' , @array) <> 0 ) BEGIN

-- patindex matches the a pattern against a string
SELECT @separator_position = PATINDEX('%' + @separator + '%' , @array)
SELECT @array_value = left(@array, @separator_position - 1)

-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
INSERT @return ([value]) SELECT @array_value

-- This replaces what we just processed with and empty string
SELECT @array = STUFF(@array, 1, @separator_position, '')
END

RETURN
END
GO

DECLARE @dataSet TABLE (
[cDef] INT
, [description] VARCHAR(8000)
)

INSERT @dataSet ([cDef], [description])
SELECT 1500, 'Hat Tie Shoes Boots'
UNION SELECT 1500, 'Tie Blue Red Cat'
UNION SELECT 1500, 'Shoes Red Fish Cup'
UNION SELECT 1650, 'Hello Seed Tree'
UNION SELECT 1650, 'Seed Pencil Paper'
UNION SELECT 1650, 'Knife Phone Tree'

SELECT * FROM @dataSet

-- Make the Map
DECLARE @map TABLE ([cDef] INT, [description] VARCHAR(8000))

-- Cursor (pah -- 2000 LONG FOR CROSS APPLY!)
DECLARE popCursor CURSOR LOCAL READ_ONLY FOR
SELECT [cDef], [description] FROM @dataSet

DECLARE @code INT
DECLARE @description VARCHAR(8000)
OPEN popCursor

FETCH NEXT FROM popCursor INTO @code, @description

WHILE ( @@FETCH_STATUS = 0 ) BEGIN

INSERT @map ([cDef], [description])
SELECT
@code
, p.[value]
FROM
dbo.CG_PARSE_ARRAY (@description, ' ') AS p
WHERE
NOT EXISTS (
SELECT 1
FROM @map AS m
WHERE
m.[cDef] = @code
AND m.[description] = p.[value]
)

FETCH NEXT FROM popCursor INTO @code, @description
END

CLOSE popCursor
DEALLOCATE popCursor

SELECT * FROM @map


-- Concatenate
SELECT
[cDef]
, (
SELECT [description] + ' '
FROM @map AS m2
WHERE m2.[cDef] = m.[cDef]
ORDER BY m2.[description]
FOR XML PATH('')
) AS [descriptions]
FROM
(
SELECT DISTINCT [cDef] FROM @map
)
AS m



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-23 : 12:13:34
Do you need to maintain the order, i.e. 1500 Hat Tie Shoes Boots Blue Red Cat Fish Cup, or could they be in alphabetical order?
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-23 : 12:15:08
FYI TransactCharlie, FOR XML PATH is a 2005 feature.
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-23 : 12:15:43
my solution reorders then alphabetically.

without the order by I think it would be random.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-23 : 12:16:28
quote:
Originally posted by robvolk

FYI TransactCharlie, FOR XML PATH is a 2005 feature.



Why does it work on my database that is compatibility level 80 then?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-23 : 12:18:17
A lot of 2005 features work in compatibility mode, but they won't work on an actual 2000 server.
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-23 : 12:19:14
ah. right. That's annoying.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

bopritchard
Starting Member

3 Posts

Posted - 2010-09-23 : 12:19:28
quote:
Originally posted by robvolk

Do you need to maintain the order, i.e. 1500 Hat Tie Shoes Boots Blue Red Cat Fish Cup, or could they be in alphabetical order?



Order doesn't matter
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-23 : 13:41:22
Well, it doesn't use a cursor:
-- creates a "Tally" table (#n) of numbers
-- skip this part if you already have a tally table, and change the name below

create table #n(n smallint not null primary key)
insert #n values(1)
while (select max(n) from #n)<8000
insert #n select n+(select max(n) from #n) from #n where n+(select max(n) from #n)<=8000

-- sample data
create table #a(code int not null, description varchar(1024) not null)
insert #a (code, description)
select 1500, 'Hat Tie Shoes Boots' union all
select 1500, 'Tie Blue Red Cat' union all
select 1500, 'Shoes Red Fish Cup' union all
select 1650, 'Hello Seed Tree' union all
select 1650, 'Seed Pencil Paper' union all
select 1650, 'Knife Phone Tree'

-- temp table to hold split words
create table #results(code int not null, word varchar(128) not null)

-- query to split words
insert #results(code, word)
select distinct code, ltrim(rtrim(substring(d, n, charindex(' ', d, n+1)-n)))
from (select code, ' '+description+' ' d from #a) z -- subquery to simplify the substring expression
cross join #n
where n<len(d) and substring(d,n-1,1)=' '

-- temp table to hold concatenated results
create table #merge (code int not null primary key, description varchar(8000))

insert #merge select code, min(word) from #results group by code

-- loop through words, concatenate, then remove from #results
while @@rowcount>0 begin
delete r from #results r
inner join #merge m on m.code=r.code
where m.description like '%'+r.word

update m set description=description+' '+r.word
from #merge m inner join
(select code, min(word) word from #results group by code) r on m.code=r.code
end

select * from #merge
The black text is the setup, the blue text is the meat of it. Change references to #a and #n as needed. This is based on an old article of mine:

http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

I still think it's kinda ugly but it should do the trick. I hate to say that a cursor approach might be faster; at least I won't write it.
Go to Top of Page
   

- Advertisement -