March 17, 2017 at 8:36 am
Hi,
I have a Cross apply table that returns different columns concatenated in one column, I want to have them separated
this is my code
if OBJECT_ID('tempdb..#answers') is not null
drop table #answers;
Create table #answers
(
id int,
value varchar(50),
)
go
INSERT INTO #answers
VALUES(1,'1,2,3,4'),
(2,'5,2'),
(3,'3,4')
GO
if OBJECT_ID('tempdb..#lookup') is not null
drop table #lookup;
Create table #lookup
(
id int,
text varchar(50),
GUID varchar (100),
)
go
INSERT INTO #lookup
VALUES(1,'txt1', 'g1'),
(4,'txt1', 'g4'),
(2,'txt1', 'g2'),
(3,'txt1', 'g3'),
(5,'txt1', 'g5')
GO
select
a.*,
ca.*
from
#answers a
cross apply (select
stuff((select
',' + l.[text] + l.GUID
from
#lookup l
inner join dbo.DelimitedSplit8K(a.[value],',') ds
on l.id = ds.Item
order by
ds.ItemNumber
for xml path(''),type).value('.','varchar(max)'),1,1,'')) ca(AnswerValue)
the code returns this result :
id | value | AnswerValue
1 |1,2,3,4 | txt1g1,txt1g2,txt1g3,txt1g4
...
I want that AnswerValue will be split in 2 columns like this result, ( I tryied union all but it s not working)
id | value | AnswerValue | AnswerGUID
1 |1,2,3,4 | txt1,txt1,txt1,txt1 |g1,g2, g3, g4
....
Thank you
March 17, 2017 at 8:44 am
Use two cross applies, one for each column.
March 17, 2017 at 8:47 am
I suspect you are not totally comfortable with what your current code is doing. You are using FOR XML to stuff l.text and l.GUID into a single column. If you want them separated you would need to stuff l.text into one column and l.GUID into another column using the same technique. The reason I didn't write this for you is because I am greatly concerned you don't understand this code very well and handing it to you is not doing you any favors.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 17, 2017 at 10:11 am
One cross apply, two FOR XML PATH concats, one for each column. I have the code, but I'll wait to see what the OP has to say.
March 20, 2017 at 7:45 am
Lynn Pettis - Friday, March 17, 2017 10:11 AMOne cross apply, two FOR XML PATH concats, one for each column. I have the code, but I'll wait to see what the OP has to say.
Hi lynn,
I make it work like that
if OBJECT_ID('tempdb..#answers') is not null
drop table #answers;
Create table #answers
(
id int,
value varchar(50),
)
go
INSERT INTO #answers
VALUES(1,'1,2,3,4'),
(2,'5,2'),
(3,'3,4')
GO
if OBJECT_ID('tempdb..#lookup') is not null
drop table #lookup;
Create table #lookup
(
id int,
text varchar(50),
GUID varchar (100),
)
go
INSERT INTO #lookup
VALUES(1,'txt1', 'g1'),
(4,'txt1', 'g4'),
(2,'txt1', 'g2'),
(3,'txt1', 'g3'),
(5,'txt1', 'g5')
GO
select va.id, va.value, va.AnswerValue, Gid.AnswerGUID
from
(
select
a.*,
ca.*
from
#answers a
cross apply (select
stuff((select
',' + l.[text]
from
#lookup l
inner join dbo.DelimitedSplit8K(a.[value],',') ds
on l.id = ds.Item
order by
ds.ItemNumber
for xml path(''),type).value('.','varchar(max)'),1,1,'')) ca(AnswerValue)
) as va
left join
(
select
a.*,
cb.*
from
#answers a
cross apply (select
stuff((select
',' + l.GUID
from
#lookup l
inner join dbo.DelimitedSplit8K(a.[value],',') ds
on l.id = ds.Item
order by
ds.ItemNumber
for xml path(''),type).value('.','varchar(max)'),1,1,'')) cb(AnswerGUID)
) as Gid
on va.id = Gid.id
March 20, 2017 at 8:00 am
You don't need to repeat the entire query again to get the second set of values. You could do this in a single query with a second cross apply.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 20, 2017 at 3:36 pm
This:
if OBJECT_ID('tempdb..#answers') is not null
drop table #answers;
Create table #answers
(
id int,
value varchar(50),
)
go
INSERT INTO #answers
VALUES(1,'1,2,3,4'),
(2,'5,2'),
(3,'3,4')
GO
if OBJECT_ID('tempdb..#lookup') is not null
drop table #lookup;
Create table #lookup
(
id int,
text varchar(50),
GUID varchar (100),
)
go
INSERT INTO #lookup
VALUES(1,'txt1', 'g1'),
(4,'txt1', 'g4'),
(2,'txt1', 'g2'),
(3,'txt1', 'g3'),
(5,'txt1', 'g5')
GO
select
a.*,
ca1.AnswerValue,
ca2.AnswerGUID
from
#answers a
cross apply (select
stuff((select
',' + l.[text]
from
#lookup l
inner join dbo.DelimitedSplit8K(a.[value],',') ds
on l.id = ds.Item
order by
ds.ItemNumber
for xml path(''),type).value('.','varchar(max)'),1,1,'')) ca1(AnswerValue)
cross apply (select
stuff((select
',' + l.GUID
from
#lookup l
inner join dbo.DelimitedSplit8K(a.[value],',') ds
on l.id = ds.Item
order by
ds.ItemNumber
for xml path(''),type).value('.','varchar(max)'),1,1,'')) ca2(AnswerGUID);
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply