U-Turn (Going the other way)

So, here's a way to get back a delimited list of items from a table. In other words, concatenate values into one column (that you can then take together to pass to my parse table function).

--Setup a sample data to demonstrate the concept
declare @Table1 as Table (id int, Item varchar(100))
insert into @Table1 values (1, 'a')
insert into @Table1 values (1, 'b')
insert into @Table1 values (2, 'c')
-- show the table data
select * from @Table1

--retrieve the data from @Table1 as a concatinated list
Select distinct id, (
Select upper(Item)+' ' as [text()] from @Table1 S 
where id = T1.id 
for xml path('')) Items  from @Table1 T1

Comments

Post a Comment

Popular posts from this blog

ATLAS AJAX Control Toolkit AutoCompleteExtender with Key Lookup

I like the ATLAS AJAX Control Toolkit. It integrates with Visual Studio and has useful if not basic javascript functions that can help a website be much more interactive. The AutoComplete Extender is used to provide auto-complete functionality to a text box. To use it, you simply add an extender and a web service method that provides a list of strings based on the text that the user started typing. So, if you are trying to provide the exact string that the user is typing (ala google search help), you can use it out of the box. However, sometimes you want more list-ish functions, that is, you want to present the user with a list of valid choices that represent objects, not just the descriptions. One alternative is to use a Listbox with a ListSearch Extender. This extender is used to find an existing value within the listBox. The downside to this approach is that every item you are looking for has to be in the list. This can be rather unwieldy if the list has more than a couple dozen i...
Read more

Database Projects, SQL Unit Tests, and TeamCity

Image
Continuous Integration  is a really good idea, especially when you are working with a team of developers. There are lots of tools out there that can help your team build better quality into your application. TeamCity by JetBrains is one such tool that has been around for awhile; specifically, it is the one that my company uses. Visual Studio has Database Projects   SQL Server Data Tools  to address some of the inconsistencies that developers face when using a database as a storage layer; Things like version control and consistent deployment. It also includes SQL unit tests , which fills a huge gap if any  of your logic is in the database. Many organizations moved away from logic in the database years ago because it was tricky (at best) to verify logic on the data tier, of course, they were also making changes directly to the database itself... but I digress. If you are using SQL Server Data Tools, and run your unit tests locally, it is a joy to behold. Your data...
Read more

Accessing a ServiceStack RESTful API using jQuery with CORS

The following applies to jQuery version 2.0.3 and ServiceStack version 3.9.59 My latest project is using ServiceStack.net  to create a REST-ful API for an existing data interchange format. There are a few gotchas that are important to understand: REST API DELETE Methods and jQuery The specification may be a bit vague, but most server frameworks (including ServiceStack) ignore any parameters passed to DELETE methods in the body. Only parameters passed on the URL are sent into the method. This behavior is similar to a HTTP GET. That is okay, but when you call an $.ajax method using jQuery, it doesn't serialize and attach the data to the URL, but instead puts it in the body...where the server promptly ignores it. In order to handle this apparent disconnect, and use the same semantics in $.ajax, you need to move the parameters from the body into the URL. The following ajax prefilter code does the trick: $.ajaxPrefilter(function (options, originalOptions, jqXHR) { if (options...
Read more