Difficult query: return recordset from concatenated strings?

[M Wells]

Hi All,

I have what seems to me to be a difficult query request for a database
I've inherited.

I have a table that has a varchar(2000) column that is used to store
system and user messages from an on-line ordering system.

For some reason (I have no idea why), when the original database was
being designed no thought was given to putting these messages in
another table, one row per message, and I've now been asked to provide
some stats on the contents of this field across the recordset.

A pseudo example of the table would be:

custrep, orderid, orderdate, comments

1, 10001, 2004-04-12, :Comment 1:Comment 2:Comment 3:Customer asked
for a brown model
2, 10002, 2004-04-12, :Comment 3:Comment 4:
1, 10003, 2004-04-12, :Comment 2:Comment 8:
2, 10004, 2004-04-12, :Comment 4:Comment 6:Comment 7:
2, 10005, 2004-04-12, :Comment 1:Comment 6:Customer cancelled order

So, what I've been asked to provide is something like this:

orderdate, custrep, syscomment, countofsyscomments
2004-04-12, 1, Comment 1, 1
2004-04-12, 1, Comment 2, 2
2004-04-12, 1, Comment 3, 1
2004-04-12, 1, Comment 8, 1
2004-04-12, 2, Comment 1, 1
2004-04-12, 2, Comment 3, 1
2004-04-12, 2, Comment 4, 2
2004-04-12, 2, Comment 6, 2
2004-04-12, 2, Comment 7, 1

I have a table in which each of the system comments are defined.
Anything else appearing in the column is treated as a user comment.

Does anyone have any thoughts on how this could be achieved? The end
result will end up in an SQL Server 2000 stored procedure which will
be called from an ASP page to provide order taking stats.

Any help will be humbly and immensely appreciated!

Much warmth,

Murray

[David Portas]

Assuming your tables look something like this:

CREATE TABLE Orders (custrep INTEGER NOT NULL, orderid INTEGER, orderdate
DATETIME NOT NULL, comment1 VARCHAR(2000) NULL, comment2 VARCHAR(2000) NULL,
comment3 VARCHAR(2000) NULL, comment4 VARCHAR(2000) NULL /*, PRIMARY KEY ???
*/)

CREATE TABLE SystemComments (comment VARCHAR(2000) PRIMARY KEY)

Try this:

SELECT O.orderdate, O.custrep, O.comment,
COUNT(S.comment) AS count_of_syscomments
FROM
(SELECT orderdate, custrep, comment1
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment2
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment3
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment4
FROM Orders)
AS O (orderdate, custrep, comment)
LEFT JOIN SystemComments AS S
ON O.comment = S.comment
GROUP BY O.orderdate, O.custrep, O.comment

--
David Portas
SQL Server MVP
--

[M Wells]

On Fri, 14 May 2004 15:51:25 +0100, "David Portas"
<RE****************************@acm.org> wrote:

Assuming your tables look something like this:

CREATE TABLE Orders (custrep INTEGER NOT NULL, orderid INTEGER, orderdate
DATETIME NOT NULL, comment1 VARCHAR(2000) NULL, comment2 VARCHAR(2000) NULL,
comment3 VARCHAR(2000) NULL, comment4 VARCHAR(2000) NULL /*, PRIMARY KEY ???
*/)

CREATE TABLE SystemComments (comment VARCHAR(2000) PRIMARY KEY)

Try this:

SELECT O.orderdate, O.custrep, O.comment,
COUNT(S.comment) AS count_of_syscomments
FROM
(SELECT orderdate, custrep, comment1
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment2
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment3
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment4
FROM Orders)
AS O (orderdate, custrep, comment)
LEFT JOIN SystemComments AS S
ON O.comment = S.comment
GROUP BY O.orderdate, O.custrep, O.comment

Hi David,

Thanks for the suggestion, unfortunately that's not how the table is
defined.

Sorry, I should have posted a pseudo create table statement as well.

It looks something like:

CREATE TABLE OrderComments (custrep INTEGER NOT NULL, orderid INTEGER,
orderdate DATETIME NOT NULL, comments VARCHAR(2000))

The create table statement you have for SystemComments is fine.

So, in the OrderComments table, the comments column might contain:

':Comment 1:Comment 2: Comment 8:Comment whatever'

So, each of the system and user generated comments for a particular
order are concatenated into a string and are put into a single column
(comments column) for that order.

Sorry for the confusion...

Much warmth,

Murray

[RSMEINER]

>So, in the OrderComments table, the comments column might contain:

':Comment 1:Comment 2: Comment 8:Comment whatever'

So, each of the system and user generated comments for a particular
order are concatenated into a string and are put into a single column
(comments column) for that order.

Sorry for the confusion...

Much warmth,

Murray

Can I assume that these are free form and free for all type of
comments and not standardized ?

Is there some kind of unique seperator between comments ?

Been there, done this real recently and it wasn't pretty at all.


Randy
http://members.aol.com/rsmeiner

[David Portas]

You can try this:

SELECT O.orderdate, O.custrep, O.comments,
COALESCE(SUM((LEN(O.comments)-LEN(REPLACE(O.comments,S.comment,'')))
/LEN(S.comment)),0)
FROM OrderComments AS O
LEFT JOIN SystemComments AS S
ON O.comments LIKE '%'+S.comment+'%'
GROUP BY O.orderdate, O.custrep, O.comments

Don't expect great performance though!

--
David Portas
SQL Server MVP
--

[M Wells]

On 14 May 2004 15:20:02 GMT, rs******@aol.comcrap (RSMEINER) wrote:

[snip]

Can I assume that these are free form and free for all type of
comments and not standardized ?

Is there some kind of unique seperator between comments ?

Been there, done this real recently and it wasn't pretty at all.

Hi Randy,

Pretty much, except that I have a reference table of the exact wording
of each of the system comments that might be found in the concatenated
value in the comments column.

The comments are delimited by a colon character, but I can't assume
that user comments, which get concatenated in the same field, will
always be lacking colon characters.

The only thing I can think to do is create a temp table in a stored
procedure and do multiple update...select statements to populate the
temp table, using the values in the predfined comments table.

I hear you that it isn't pretty.

Much warth,

Murray

[RSMEINER]

>Hi Randy,

Pretty much, except that I have a reference table of the exact wording
of each of the system comments that might be found in the concatenated
value in the comments column.

The comments are delimited by a colon character, but I can't assume
that user comments, which get concatenated in the same field, will
always be lacking colon characters.

The only thing I can think to do is create a temp table in a stored
procedure and do multiple update...select statements to populate the
temp table, using the values in the predfined comments table.

I hear you that it isn't pretty.

Much warth,

Murray

Since you have a table of the system comments, it makes it
much easier. I'm thinking on this.

How big are these tables ?

Randy
http://members.aol.com/rsmeiner

[David Portas]

Did you try my second solution?

--
David Portas
SQL Server MVP
--