I have created a CTE query, and I manage to join the CTE when the data in CTE is match with another table.
For example, this is how my CTE query result looks like:
ID NAME REG INV CUS BR
-----------------------------------------------
1 A0001 R0001 I0001 C0001 B0001
2 A0002 R0002 I0002 C0002 B0002
3 A0003 R0003 I0003 C0003 B0003
4 A0004 R0004 I0004 C0004 B0004
And this is the table I manage to join it to:
ID NAME CUS
---------------------
1 TEST1 C0001
2 TEST2 C0002
3 TEST3 C0003
4 TEST4 C0004
And this is my code to select CTE query
;WITH BaseQuery AS
(
SELECT
Id, Name, Comment,
CONVERT(XML, '<root><item>'
+ REPLACE(
REPLACE(
REPLACE(
REPLACE((SELECT Comment AS '*' FOR XML PATH('')),
'Reg:',
'</item><item type="Reg">'),
'Inv:',
'</item><item type="Inv">'),
'Cus:',
'</item><item type="Cus">'),
'Br:',
'</item><item type="Br">') + '</item></root>') CommentAsXml
FROM
GenTransaction
), Query (
SELECT
Id, Name, Comment,
Reg = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Reg"])[1]', 'VARCHAR(11)'))),
Inv = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Inv"])[1]', 'VARCHAR(11)'))),
Cus = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Cus"])[1]', 'VARCHAR(11)'))),
Br = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Br"])[1]', 'VARCHAR(11)')))
FROM
BaseQuery bq
)
select ArCustomer.Name, Query.Cus from ArCustomer left join Query on ArCustomer.Customer = Query.ArCustomer.Customer order by ArCustomer.Name
[ 1 ] Using two CTEs (BaseQuery, Query and join between table and Query):
;WITH BaseQuery AS
(
SELECT
Id, Name, Comment,
CONVERT(XML, '<root><item>'
+ REPLACE(
REPLACE(
REPLACE(
REPLACE((SELECT Comment AS '*' FOR XML PATH('')),
'Reg:',
'</item><item type="Reg">'),
'Inv:',
'</item><item type="Inv">'),
'Cus:',
'</item><item type="Cus">'),
'Br:',
'</item><item type="Br">') + '</item></root>') CommentAsXml
FROM
GenTransaction
), Query (
SELECT
Id, Name, Comment,
Reg = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Reg"])[1]', 'VARCHAR(11)'))),
Inv = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Inv"])[1]', 'VARCHAR(11)'))),
Cus = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Cus"])[1]', 'VARCHAR(11)'))),
Br = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Br"])[1]', 'VARCHAR(11)')))
FROM
BaseQuery bq
)
SELECT ...
FROM Table1 t1 INNER/LEFT OUTER/... JOIN Query q ON ... join condition ... -- Query represents the second CTE
ORDER BY ...
[ 2 ] Second solution is based also on two CTEs (BaseQuery and Query) but instead of JOIN is using APPLY operator thus:
;WITH BaseQuery AS
(
SELECT
Id, Name, Comment,
CONVERT(XML, '<root><item>'
+ REPLACE(
REPLACE(
REPLACE(
REPLACE((SELECT Comment AS '*' FOR XML PATH('')),
'Reg:',
'</item><item type="Reg">'),
'Inv:',
'</item><item type="Inv">'),
'Cus:',
'</item><item type="Cus">'),
'Br:',
'</item><item type="Br">') + '</item></root>') CommentAsXml
FROM
GenTransaction
), Query (
SELECT
Id, Name, Comment,
Reg = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Reg"])[1]', 'VARCHAR(11)'))),
Inv = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Inv"])[1]', 'VARCHAR(11)'))),
Cus = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Cus"])[1]', 'VARCHAR(11)'))),
Br = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Br"])[1]', 'VARCHAR(11)')))
FROM
BaseQuery bq
)
SELECT ... t1.Col1 ... x.Col2 ...
FROM Table1 t1
OUTER/CROSS APPLY (
SELECT ...
FROM Query q
WHERE ... join condition ... -- Query represents the second CTE
) x
ORDER BY ...
[ 3 ] Another solution is to insert those rows (extracted from Comment column) into a temp table (#Results) and then JOIN temp (#Results) table with another table (Table1):
;WITH BaseQuery AS
(
SELECT
Id, Name, Comment,
CONVERT(XML, '<root><item>'
+ REPLACE(
REPLACE(
REPLACE(
REPLACE((SELECT Comment AS '*' FOR XML PATH('')),
'Reg:',
'</item><item type="Reg">'),
'Inv:',
'</item><item type="Inv">'),
'Cus:',
'</item><item type="Cus">'),
'Br:',
'</item><item type="Br">') + '</item></root>') CommentAsXml
FROM
GenTransaction
)
SELECT
Id, Name, Comment,
Reg = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Reg"])[1]', 'VARCHAR(11)'))),
Inv = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Inv"])[1]', 'VARCHAR(11)'))),
Cus = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Cus"])[1]', 'VARCHAR(11)'))),
Br = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Br"])[1]', 'VARCHAR(11)')))
INTO #Results
FROM
BaseQuery bq; -- ORDER BY here should be used within final query
SELECT ...
FROM Table1 t1 INNER/LEFT OUTER/... JOIN #Results r ON ... join condition build using t1./r... ...
ORDER BY ...
Id, Name, Comment
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments