Subscribe to this thread
Home - General / All posts - Concatenate many rows into single text string
2010MH5 post(s)
#01-Feb-13 23:26

is there a way to concatenate multiple rows into a single string by using a query?

For example

Original table

SubjectID;StudentName

1 ; Mike

1 ; John

1 ; Steve

2 ; Emily

2 ; Brent

New Table

Subject ID;StudentName

1;Mike, John, Steve

2;Emily, Brent

mlinth
447 post(s)
#02-Feb-13 13:45

Yes, I believe you can...

UPDATE

(Select n.[Subject ID], n.[StudentName] as allnames, o.[StudentName] as onename

 FROM [NewTable] n 

 INNER JOIN [OriginalTable] o on o.[Subject ID]= n.[Subject ID])

SET allnames= allnames & ", " & onename

Got this from here. Apologies for any typos...

Oh, and you need to create the new table first and fill it with the subject ids...

2010MH5 post(s)
#05-Feb-13 19:11

You've just ended countless hours of torment

Many Thanks!!

volker

1,086 post(s)
#28-Mar-15 19:35

this query rescue my project, before i don`t know how to solve this.

But now i am on the point i use this query in a way that`s not handly

very friendly. Have a look at the attachement.

Maybe there`s a way to solve my problem (have a look at the Comment) ?

Attachments:
concatenate_TEST.map


http://www.thegisservicesector.de

dchall8
1,008 post(s)
#21-Apr-15 21:00

I was going to start a new thread for my question, but really it's just slightly different from the original question.

Once a month I export a .kml file from one of my drawings. Even with my cheat sheet for formatting, it takes me about 30 minutes each time to create a meaningful pop up box for Google Earth. All I need is one typo in the process and I have to start over. What I do is Append a mix of HTML text and fields from my drawing into a text field I create just for this kml export. I won't bore you with the entire list of stuff that goes into it, but since I am a total SQL lame-o, how would I do this? Here are the first few lines of my cheat sheet. The [bracketed items] are fields in the table. The <> are HTML codes. And could I clear the field prior to refilling it?

<b>Owner:</b><br>

[file_as_na]

<br><br><b>Owner's Address:</b><br>

[addr_line2]

<br>

[addr_city]

,

[addr_state]

...and it goes on with more fields and HTML. Can you just "auto append" info into a field? If I can do this automatically, I'll run the Google Earth export every week instead of every month.

dchall8
1,008 post(s)
#29-Apr-15 14:53

Necessity caused me to research and answer my question. Trying to understand SQL for me is like trying to read Greek, so please be gentle with me. Here's the start of my query to create a text pop-up for Google Earth.

UPDATE WorkingParcels set GoogleEarthText = "<hr><b>Owner:</b><br>" & file_as_name & "<br><br><b>Owner's Address:</b><br>" & addr_line2 & "<br>" & addr_city & ", " & addr_state & "<br><br><b>Legal Description:</b><br>" & legal_desc & "<br><br><b>Situs Address:</b><br>" & situs_num & " " & situs_street_prefx & " " & situs_street & " " ...and it goes on.

Where WorkingParcels is my table name and GoogleEarthText is the field I was filling with a mix of table data and HTML coding. Generally this is an Update query which replaces the data in the GoogleEarthText field with the most current information in the database. The HTML coding is used in Google Earth for labels and formatting. In the query that coding goes in between quotes and the table field names just go in between the ampersands. I think I can see the SQL professionals' jaws dropping at the simplicity, but for me this was a milestone. This query allows me to skip about 30 minutes of manually going through this with the Transform - Append tool. Now I can knock out Google Earth overlays any time. Before I was only doing it once a month.

An example of the pop-up is attached. Hope this helps someone else using Google Earth

Attachments:
Google Earth Sample.jpg

Gustavo Palminha

1,010 post(s)
#30-Apr-15 13:42

Can you just "auto append" info into a field? If I can do this automatically, I'll run the Google Earth export every week instead of every month.

Why not creating an active column that grabs the other columns data and returns the html kml description for you.

If you provide a sample data it's easier to show how this works.


Stay connected:

Linkedin >> http://www.linkedin.com/in/gustavopalminha

Twitter >> http://twitter.com/gustavopalminha

Other >> http://www.spatialmentor.com

dchall8
1,008 post(s)
#30-Apr-15 16:08

I'm prepared to be dazzled...again. My Google Earth column is in this one. Making it an active column would make this one step jazzier. The Acreage column is normally active in my maps, so I have some familiarity with the concept. Using it for text is something I had never considered. Now I'm going to have to look into other ways to use active columns.

Attachments:
Test for Active Column.map

Gustavo Palminha

1,010 post(s)
#30-Apr-15 19:21

Attached you can find a map file like yours, but your drawing now contains an active column called kmlDesc which will get updated when you Recompute it.

You should open the table drawing script that show how this is done once you recompute the column.

Feel free to tweak it as you need.

The contents of this column, a valid sample of HTML text properly formatted, was created using http://www.quackit.com/html/online-html-editor/ online editor with the help ofhttp://www.w3schools.com/tags/tag_div.asp to check the definition of some HTML tags.

The ideia is that one you open your drawing and finish changing the column data, youcan recompute this kmlDesc that will get updated.

Regards.

Attachments:
Test for Active Column Gustavo.map


Stay connected:

Linkedin >> http://www.linkedin.com/in/gustavopalminha

Twitter >> http://twitter.com/gustavopalminha

Other >> http://www.spatialmentor.com

dchall8
1,008 post(s)
#30-Apr-15 21:12

Wow. I mean WOW! There's so much to learn it's exhausting. Thanks and thanks for gratis modernizing my HTML. I learned basic HTML in the late 90s and have not kept up with what been deprecated.

I have a couple questions...

Why not make this one long line?

Why did you use If statements for the address?

I'm not familiar with Visual Basic. Is the vbNewLine important only within the script/code itself? It doesn't seem to have an effect on the resulting content of the field.

Thanks again Gustavo. Now I'm going to have to look at how to tune this up. You don't happen to know if CSS works inside Google Earth, do you?

Gustavo Palminha

1,010 post(s)
#02-May-15 02:06

In short.....

Why not make this one long line?

If you do it as I've shown, your the code will be more readable and its easier to format the HTML. One liner is not easy to fix or debug.

Why did you use If statements for the address?

I noticed that your drawing columns, contained 3 that should include address information so I wanted to make sure that your HTML was well formatted and did not include and empty lines bellow the "address information". In addition to this I also took this opportunity to show that you can include a bit of logic when retrieving a result.

Is the vbNewLine important only within the script/code itself

No you it's not. If you want you can remove it. If you copy the text of one cell and place it inside notepad you will see that you will get new lines and in case you have any typo in your HTML its easier to fix.

You don't happen to know if CSS works inside Google Earth, do you?

I believe so and the documentation confirms it. Just check https://developers.google.com/kml/documentation/kmlreference?csw=1#description

Regards.


Stay connected:

Linkedin >> http://www.linkedin.com/in/gustavopalminha

Twitter >> http://twitter.com/gustavopalminha

Other >> http://www.spatialmentor.com

dchall8
1,008 post(s)
#05-May-15 16:36

Excellent! Thank you so much!!

I did read that page on the Google Earth documentation but did not see the part about CSS. I guess that's what the browser's page search is good for. I'll be tuning this up at my first moment of spare time.

dchall8
1,008 post(s)
#06-May-15 23:13

I did a little more work on my active column design for export to Google Earth. I'm attaching the function as well as an image of what it looks like in Google Earth.

I'd like to be able to make clickable objects to link back to our office website, but the links seem to reference nonexistent files on my computer. I have more research to do on that topic.

Thanks Gustavo.

Attachments:
Function for Manifold to create the text balloon in Google Earth.txt
Img of Google Earth for Manifold Forum.jpg

Gustavo Palminha

1,010 post(s)
#07-May-15 11:54

After taking a look into your text file with the functions I notice that you have a few bugs/errors inside which I'll name bellow:

  • You start with Function computeKMLDesc and end the file with End Function but around the middle you have again Function computeKMLDesc without closing the function with End Function.

  • I assume that you want to produce a clickable link that points to an URL based on a property ID stored inside a column. If that's the case you have to produce a properly formatted anchor tag which is not happening at the moment.

    Useful resources to help you understand what you need to know.

    • CSS Tutorial -http://www.w3schools.com/css/
    • HTML Tutorial -http://www.w3schools.com/html/
    • VBScript Tutorial - http://www.w3schools.com/vbscript/

    You should check the above resources specially the HTML and VBscript to help you understand the HTML/VBscript syntax.


    Stay connected:

    Linkedin >> http://www.linkedin.com/in/gustavopalminha

    Twitter >> http://twitter.com/gustavopalminha

    Other >> http://www.spatialmentor.com

  • dchall8
    1,008 post(s)
    #08-May-15 14:20

    It looks like the text was pasted into my text document once and then partially pasted again. I'll fix that and repost. I also want to show the click-link and post the results of that.

    dchall8
    1,008 post(s)
    #08-May-15 16:35

    I'm trying to get the active column script in Manifold to include a properly formatted HTML link. The problem is in getting the quotes in the HTML to appear correctly all the way through to Google Earth. For example I need to use the HTML a-tag with the following syntax

    a href="http://..."

    with the open and close carets at each end. When I use &quot: I get &quot; instead of " . When I use "" I get a syntax error. The following script gives me the &quot;

    KMLContent = KMLContent & "<p>Click <a href=" & "&quot;http://propaccess.banderaproptax.org/clientdb/Property.aspx?prop_id=" & Record.Data("Prop_ID") & "&quot;> HERE</a>

    Ultimately I want it to read, Click HERE to find this parcel at the Bandera County Appraisal website.

    I've attached an image from Google Earth. You can see the balloon appears correctly but the syntax for the a clickable HERE tag is not correct. I need the Manifold script to create the necessary quotes in the a tag.

    Attachments:
    Img 2 of Google Earth for Manifold Forum.jpg

    tjhb
    10,094 post(s)
    #08-May-15 22:29

    Try this (not tested).

    KMLContent = KMLContent & "<p>Click <a href=" & Chr(34) & "http://propaccess.banderaproptax.org/clientdb/Property.aspx?prop_id=" & Record.Data("Prop_ID") & Chr(34) & ">HERE</a>"

    dchall8
    1,008 post(s)
    #13-May-15 20:31

    That syntax worked. Thank you.

    Now I'm getting an error from my web database, so I'm asking them how to go directly to the db without going first through our website.

    Thanks again for all your help.

    Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.