sql - Correlation name'Split' is specified multiple times in a FROM clause -
i trying break comma seperated columns in rows here last asked question
i got solution asked suppose if want same on multiple columns
here trying
select operationtypeid ,[user],[action], split.a.value('.', 'varchar(100)') changedcolumns, split.b.value('.', 'varchar(100)') oldvalue, split.c.value('.', 'varchar(100)') newvalue ( select cast ('<x>' + replace(newvalue, ',', '</x><x>') + '</x>' xml) newvalue , cast ('<x>' + replace(oldvalue, ',', '</x><x>') + '</x>' xml) oldvalue, operationtypeid ,[user],[action] ,cast ('<x>' + replace(changedcolumns, ',', '</x><x>') + '</x>' xml) changedcolumns allrows)as outer apply changedcolumns.nodes ('/x') split(a) outer apply oldvalue.nodes ('/x') split(b) outer apply newvalue.nodes ('/x') split(c)
it gives error
the correlation name 'split' specified multiple times in clause.
change query this. cannot have same correlation name convert different columns rows
select operationtypeid, [user], [action], split.a.value('.', 'varchar(100)') changedcolumns, split1.b.value('.', 'varchar(100)') oldvalue, split2.c.value('.', 'varchar(100)') newvalue (select cast ('<x>' + replace(newvalue, ',', '</x><x>') + '</x>' xml) newvalue, cast ('<x>' + replace(oldvalue, ',', '</x><x>') + '</x>' xml) oldvalue, operationtypeid, [user], [action], cast ('<x>' + replace(changedcolumns, ',', '</x><x>') + '</x>' xml) changedcolumns allrows)as outer apply changedcolumns.nodes ('/x') split(a) outer apply oldvalue.nodes ('/x') split1(b) outer apply newvalue.nodes ('/x') split2(c)
Comments
Post a Comment