Creating multiple lines in a View

[RikHess]

I have a situation where we need to dynamically be able to convert from one table where multiple items are concatenated in a field. We need to have the output have one line per item to query the results.

The original table is supplied by a vendor as part of their app and we can't change it.

I was thinking a View may provide the dynamic element, providing on-time changes from the original table, but I do not know how/if a View can create multiple output lines for one input line.

To describe what I mean, here is an example:

Input
Date	        User	MemberID	Change
9/30/2007	Sally	123456789	FirstChange; SecondChange; ThirdChange

Output
Date	        User	MemberID	Change
9/30/2007	Sally	123456789	FirstChange
9/30/2007	Sally	123456789	SecondChange
9/30/2007	Sally	123456789	ThirdChange

Any suggestions are welcome. TIA!

 

[mrdenny]

You would need to use a table function to break apart the change column.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[gmmastros]

You may not be able to do this in a View, but you can certainly do this within a stored procedure. Here's a similar thread that you may find useful. It's not exactly what you want, but will hopefully point you in the right direction. I urge you to review this thread, attempt to come up with a solution. If you get stuck, post back here.

thread183-1243232

Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[RikHess]

Thanks for the tips.
After reporting these results, the project was scrapped.

Thanks for the effort!

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com