Posted in Software Development

Concatenate values from multiple rows with self join on SQL Server

Due to some inheritance limitations when using LinqSQL I have recently needed to do something quite strange in a SQL View which made more sense in Linq. However I never say no to a SQL challange and a few horus later a rather beautiful (in my opinion) solution was finished.

First lets look at some example data for my problem. The following data is a table off ladies attending a Mother – Daughter Event

Name Mother
Anna Fiona
Barbara
Chloe Barbara
Dani Barbara
Ellie Barbara
Fiona

The view we would like to get is something like this

Name Mother Daughters
Anna Fiona
Barbara  Chloe,Dani,Ellie,
Chloe Barbara
Dani Barbara
Ellie Barbara
Fiona  Anna,


SELECT people.name AS Name,
people.mother AS Mother,
(SELECT people_inner_selfjoin.name+','
FROM people
LEFT OUTER JOIN people people_inner_selfjoin
ON people_inner.name= people_inner_selfjoin.mother
WHERE people.name = people.mother
FOR XML PATH('')
) AS Daughters
FROM people

*You could add a substring to get rid of the final , but in my case this wasn’t required with the C# string.Split method

I found the best way to understand was to give it a go with some data and provided you understand the way joins work you can manipulate it to suit your needs.

Naturally the above is just demo data etc. in my actual implementation I also needed to include a case statement as the second column was also populated, if not with a parent then by itself, so the data had to be cleaned up even more.

I learnt something doing this hope it helps.

Advertisements
Posted in Software Development

My Java gets Clingy to UPnP (Using Java Cling for UPnP Communication)

I’m fairly new to Java programming but I don’t like to dance about with silly little projects, I’ve mastered my connection to mySQL and I’ve got a basic serial communications module. But this weeks task has been to gets some UPnP communications working, with the aim of being able to set up some port forwarding on a regular router from a basic linux (Debian) server with the settings editable in a simple web based GUI.

In a previous version I have scripted CLI calls to UPnPc from PERL but not only was this method going round the houses, it also was not the most reliable method of getting the port forwarding set up correctly. When I found Cling over the weekend I thought I had found the answer. Though so far I’ve found Cling an incredibly complicated API to use after a attempts of jumping in feet first I took a step back and re-read the manual implementing the first example:

package ...;

import org.teleal.cling.model.message.header.STAllHeader;
import org.teleal.cling.model.meta.LocalDevice;
import org.teleal.cling.model.meta.RemoteDevice;
import org.teleal.cling.registry.Registry;
import org.teleal.cling.registry.RegistryListener;

import org.teleal.cling.UpnpService;
import org.teleal.cling.UpnpServiceImpl;

/**
* Runs a simple UPnP discovery procedure.
*/
public class Main {

public static void main(String[] args) throws Exception {

// UPnP discovery is asynchronous, we need a callback
RegistryListener listener = new RegistryListener() {

public void remoteDeviceDiscoveryStarted(Registry registry,
RemoteDevice device) {
System.out.println(
"Discovery started: " + device.getDisplayString()
);
}

public void remoteDeviceDiscoveryFailed(Registry registry,
RemoteDevice device,
Exception ex) {
System.out.println(
"Discovery failed: " + device.getDisplayString() + " => " + ex
);
}

public void remoteDeviceAdded(Registry registry, RemoteDevice device) {
System.out.println(
"Remote device available: " + device.getDisplayString()
);
}

public void remoteDeviceUpdated(Registry registry, RemoteDevice device) {
System.out.println(
"Remote device updated: " + device.getDisplayString()
);
}

public void remoteDeviceRemoved(Registry registry, RemoteDevice device) {
System.out.println(
"Remote device removed: " + device.getDisplayString()
);
}

public void localDeviceAdded(Registry registry, LocalDevice device) {
System.out.println(
"Local device added: " + device.getDisplayString()
);
}

public void localDeviceRemoved(Registry registry, LocalDevice device) {
System.out.println(
"Local device removed: " + device.getDisplayString()
);
}

public void beforeShutdown(Registry registry) {
System.out.println(
"Before shutdown, the registry has devices: "
+ registry.getDevices().size()
);
}

public void afterShutdown() {
System.out.println("Shutdown of registry complete!");

}
};

// This will create necessary network resources for UPnP right away
System.out.println("Starting Cling...");
UpnpService upnpService = new UpnpServiceImpl(listener);

// Send a search message to all devices and services, they should respond soon
upnpService.getControlPoint().search(new STAllHeader());

// Let's wait 10 seconds for them to respond
System.out.println("Waiting 10 seconds before shutting down...");
Thread.sleep(10000);

// Release all resources and advertise BYEBYE to other UPnP devices
System.out.println("Stopping Cling...");
upnpService.shutdown();
}
}

This gave me hope as it worked after adding only two missing import statements (included in the above). I’ve started picking it apart and working through understanding the code. 

When I have a next step to share I will add extra details. Maybe you can learn with me.

Edit: since this post I have given up with Cling and successfully finished the development using CyberLink for Java by Satoshi Konno it can be found at http://www.cybergarage.org/. I will see about a post on this eventually.