469,112 Members | 2,026 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,112 developers. It's quick & easy.

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

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.
Jul 20 '05 #1
2 1758
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
Jul 20 '05 #2
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.

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

15 posts views Thread by gunnar.sigurjonsson | last post: by
3 posts views Thread by rahmawaktu | last post: by
5 posts views Thread by Trevisc | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.