Multi Value Values in Varchar field, to show each value separately in a view

[ImraneA]

Hi there

Got a interesting problem (depends on a point of view.....).

Background :-
Agent (within Domino) is run daily basis which extracts data from
Domino Notes application to SQL Server 2000 database. Agent first
removes ALL contents and then appends ALL data.

Reporting Tool is SQL Reporting Services (very cool !!).

Problem :-
Within Domino Notes, it can have a field which is mult-value field
i.e. contain multi values
e.g.

(from Helpdesk application)

!HowTo!!Access Email;
!HowTo!!Access the Web;
etc..

Need to create a view, then use sql to create stored proc, to be used
as the dataset for report within SQL Reporting services.

Format. (using above as data as example)

!HowTo!!Access Email; (1 row) WWL/SDR/04023/010 (
DocID)

!HowTo!!Access the Web; (2 row) WWL/SDR/04023/010 (
DocID)

I have a unique KEY within table called DocID. The report will have 8
separate datasets (i.e. using subreports), all linked back to Unique
Key. That's easy.

The dataset causing me hassle is the 1 above. How do u split out
values as separate rows ?

Name of field called --> "ImpFunctionsImpacted".

Also, notes expert, who's working on the agent, tell's me the data can
be split either as a comma or semi-colon.

Any suggestions most welcome.

[Erland Sommarskog]

ImraneA (i.*****@weir.co.uk) writes:

Problem :-
Within Domino Notes, it can have a field which is mult-value field
i.e. contain multi values
e.g.

(from Helpdesk application)

!HowTo!!Access Email;
!HowTo!!Access the Web;
etc..
...
The dataset causing me hassle is the 1 above. How do u split out
values as separate rows ?

The best bet would be to write a table-valued function for the task.
Alas, in SQL2000, you can't use that in a query to unpack a table column
easily, but you would have to do some looping to do row by row.

On http://www.sommarskog.se/arrays-in-sql.html I discuss various ways
of unpacking delimited lists into tables. The functions in the article
may not work out of the box for you, but you should be able to adapt
them to your needs. Check also out the short section "Unpacking a Table
Column" which gives a few more ideas for this situation.

And if you feel that this becoming a whole lot of work, you are absolutely
right. This definitely not the way to store data relationally.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[ImraneA]

Thanks for comments. I follow it up..

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

ImraneA (i.*****@weir.co.uk) writes:

Problem :-
Within Domino Notes, it can have a field which is mult-value field
i.e. contain multi values
e.g.

(from Helpdesk application)

!HowTo!!Access Email;
!HowTo!!Access the Web;
etc..
...
The dataset causing me hassle is the 1 above. How do u split out
values as separate rows ?

The best bet would be to write a table-valued function for the task.
Alas, in SQL2000, you can't use that in a query to unpack a table column
easily, but you would have to do some looping to do row by row.

On http://www.sommarskog.se/arrays-in-sql.html I discuss various ways
of unpacking delimited lists into tables. The functions in the article
may not work out of the box for you, but you should be able to adapt
them to your needs. Check also out the short section "Unpacking a Table
Column" which gives a few more ideas for this situation.

And if you feel that this becoming a whole lot of work, you are absolutely
right. This definitely not the way to store data relationally.