From the dark depths of sql comes ……..

Its surprising the things that you can find and do with sql, especially MS SQL. My particular example comes from a little exercise that i had to perform.

The exercise seemed pretty simple at face value. I was to select all the required data from a certain table for a report but I had to add an extra column at the end that contained all the child record ID’s for that specific record. The ID’s had to be comma separated so that it could be interpreted by who ever was going to read it.

So the digging begins. I began with the usual inner joins which did not really help at all. After much googling i came across the coalesce command, which in the examples I saw seemed to do what I wanted. After trying it out it seemed that it would not work as it returned only a single column and required me to create a function or stored procedure that would return me a value that I wanted.

After searching again I finally hit gold and got a name for the action that I was trying to perform. Before I explain the answer this is an example of what i was doing . e.g.

Tables available:

Parent Table 1
————-
ID
Name

Child Table 1
————
ID
ParentID
ChildName

The select was to return the following when doing a select from the parent table.

ID | Name | ChildIDs
—————-
1 | Joe | 1,2,3,4

This all needed to be done using no user created functions or stored procedures.

The Solution:

Select ID,Name,ChildID from ParentTable
Cross Apply(
select cast(ID as varchar) + ‘,’ from ChildTable where ParentID = ParentTable.IS
for XML PATH(”)
) Temp (ChildID)

What this does is perform string aggregation on my data as seen above. This is done using the little known cross apply operator and the for XML operator.
Both are very under used and not particularly known. The XML Path is an easy way to generate xml from and sql statement , this is best used when serializing data in and out of an SQL Database.

What really amazed me was that how simple this is to do in oracle. I initially only programmed against oracle databases so it may be just a case of me getting up to speed with MS SQL databases, but I want to have a look at SQL 2008 as it seems that MS has put a lot of work into this and it could turn out to be really interesting.

As always if you know a better way to do something listed here, please let me know. Also, I am not the perfect SQL programmer. I do what I deem the best way. Please don’t bash me based on this.

Thanks
~stalkerh