sql - TSQL - Subselect to Create column and value from multiple values -
i didn't explain title i'm trying achieve following:
i'm querying 2 tables devices , installed applications , returning applications device has associated. i'm trying figure out how search returned applications , if office 2010 found returns column called "officeappinstalled" , result of 'office 2010 only' or if office 2010 , office 2013 both found result of "office 2010&2013" created.
my sql not , fiddle missing restraints primitive of data getting outputed. sqlfiddle.com/#!3/7175a/5/0 i'd new column called 'officeinstalled' , code in sqlfiddler computer1 1 row resulted officeinstalled column value of office 2010/2013.
after taking @ table structure seems way compare appname join on same table comparing appname parsing through string values. using string parsing compares in sql in general, bad , inconsistent.
assuming have ability adjust/add table structure change tapplications remove join tdevices.
then can create joining table joins tapplications tdevices many many , housing 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 structure allows sort application name versions , can group application , combine each version single record.
edit: misunderstood question, here query believe looking for:
select stuff((select distinct ', ' + appname tapplications ta2 ta2.description = ta.description xml path('')), 1, 2, '') appname, ta.description, td.name tapplications ta join tdevices td on ta.tdevice_index = td.id td.name = 'computer1' group ta.description, td.name
here working update sql fiddle http://sqlfiddle.com/#!3/7175a/30/0
Comments
Post a Comment