ZGodwin February 2016

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.

Answers


MCronin February 2016

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,
     ApplicationID int,
     DeviceID int,
     Version nvarchar(256),
     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.

Edit: 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, 
            ta.description, 
            td.name
    from TApplications ta 
      join tdevices td on ta.TDevice_Index = td.id
    WHERE td.name = 'Computer1'
    GROUP BY ta.description, td.name

Here is working update to your Sql fiddle http://sqlfiddle.com/#!3/7175a/30/0

Post Status

Asked in February 2016
Viewed 3,951 times
Voted 14
Answered 1 times

Search




Leave an answer