TSQL - Subselect to Create column and value from multiple values
I probably didn't explain the title well but what I'm trying to achieve is the following:
I'm querying two tables devices and installed applications and returning all the applications that device has associated. I'm trying to figure out how to search the returned applications and if Office 2010 is found it returns something like a column called "OfficeAppInstalled" and a result of 'Office 2010 only' or if Office 2010 and Office 2013 are both found a result of "Office 2010&2013" is created.
My SQL is not very good and this fiddle is missing restraints but this is a primitive of the data getting outputed. sqlfiddle.com/#!3/7175a/5/0 I'd like a new column called 'OfficeInstalled' and from the code in sqlfiddler for Computer1 only one row would be resulted with the OfficeInstalled column with a value of Office 2010/2013.
After taking a look at your table structure it seems that the only way to do this would be to compare appname to a join on the same table comparing to appname by parsing through the string values. Using string parsing compares in SQL is in general, bad and inconsistent.
Assuming you have the ability to adjust/add to the table structure you could change TApplications to remove the join to TDevices.
Then you can create a joining table that joins TApplications to TDevices as a many to many and housing the version.
CREATE TABLE TApplicationsTDevices
id INT IDENTITY Primary Key,
FOREIGN KEY (ApplicationID ) REFERENCES TApplications (id),
FOREIGN KEY (DeviceID ) REFERENCES TDevices(id)
Having a structure like this allows you to sort by application name as well as versions and you can easily group by application and combine each version into a single record.
I misunderstood the question, here is the query that I believe you are looking for:
select STUFF((SELECT DISTINCT ', ' + appname
FROM TApplications ta2
WHERE ta2.description = ta.description
FOR XML PATH('')), 1, 2, '') AS appname,
from TApplications ta
join tdevices td on ta.TDevice_Index = td.id
WHERE td.name = 'Computer1'
GROUP BY ta.description, td.name