Add Column to view that Concatenate 2nd view multiple records

[Rx_]

See attachment:
Primary View with PK [ID_Wells]
2nd View sorted by [ID_Wells] can have 0 to many text codes
Objective:
Add a new field to Primary View that Concatenates the character codes in the records where the [ID_Wells] match. This can be 0 to many.

See attachment for example of output.
In case no data matches, a Null would be preferred over an empty character.

In an Access Query, this was accomplished by writing a custom function in VBA.
For SQL, this looks a little daunting at first.

After reviewing a few MSDN articles, this seems to get more complex than first expected.

Any ideas would be appreciated.

 

[pr2-eugin]

You could use Functions in SQL could you not? translating the function using the Allen Browne's ConcatRelated should be easy (I guess)?

How about this thread? http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string

 

[pbaldy]

Somewhere around here I've got a function that does that. I'll try to find it.

 

[pbaldy]

I don't remember seeing that link; either I'm blind or Paul added it after. I wanted credit given where due, so I dug up the thread I found an answer on. I've used versions of the function posted by blindman in post 2 several times:

http://www.dbforums.com/showthread.php?1605725-String-Concatenation-using-CURSORs

 

[Rx_]

Pbaldy - A Cursor solution - That is slick. It is much better than the examples I searched for. Please bear with me on this.
The advantage of the Function would be to use the function like in an MS Access Query. The ID is passed in and the string results show up in the field for that ID.


pr2-eugin had a suggestion that was the same site I ended up with.
I was busy putting together something that worked (code posted below)
The implementation would be different. My thoughts are to create a View using the code below. Then, the View would be added in the design of the existing view to bring in this single field.

Here is my problem. The following Select STIP2 works by itself.
- I tried to create a new View using it. The View won't accept it including the ORDER BY clause.
- I can't seem to figure out how to insert it into the existing view's field name

Working Query - but I can't seem to create a view so it could be joined to Primary View by ID_Wells

SELECT STIP2.[ID_Wells], 
    substring(
        (
            Select ', '+STIP1.[Stip_Abv]  AS [text()]
            From [RegulatoryDB].[dbo].[vRegulatory_Nav_GIS_Stips] STIP1
            Where STIP1.[ID_Wells] = STIP2.[ID_Wells]
            ORDER BY STIP1.[ID_Wells]
            For XML PATH ('')
        ), 2, 1000) [Stip_Abv]
FROM [RegulatoryDB].[dbo].[vRegulatory_Nav_GIS_Stips] STIP2
-- Works stand-alone returns ID_Wells and the Concat STIPS

example of a query that the above would be nice to insert into as a new Row

SELECT TOP 1000 [Reg_ID_Wells]
      ,[Well Name]
      
  FROM [RegulatoryDB].[dbo].[vRegulatory_Nav_GIS]
  order by [Reg_ID_Wells]

I am so close but can't seem to match these.
Maybe I have to go with the function after a short Break.

 

[Rx_]

Well, a cup of coffee strong enough to make me ski uphill did the trick and eating a pie: Let the Pie Work. (Men in Black - movie)
After the break, I took the concat code above and put it into the SQL payne of a New View. Once again, SQL came back with an error that it couldn't parse the query. This time, I chose Continue. It displayed perfectlly in the View Query Results grid.
After saving and naming it, it is in my Views object list.
It works just fine.
With this view of the PK ID Well and the Stip_Abv (the concat string), it can be joined.
Why did I believe the Microsoft SSMS Create View error message?

THANKS!

I am going to start a new question and look at that Function solution.
This was the easy one.