CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3239820
user3239820

Reputation:

Rows to comma separated values using XML tag

I have 2 tables named 'Categories' and 'CSTagContent' shown below with data...

TABLE 1: 'Categories'

CategoryID  PostID  <----Categories Table
 1148       581771  
 1183       581771  
 1184       581771

TABLE 2: 'CSTagContent'

ID   TagContent       StartDate   EndDate   CategoryID      TagTitle     <---CSTagContent Table

 1 <blockquote><p>     2014-11-08 2014-11-14  1148       
   <a href="abc.com">
   </p></blockquote>
 2 <blockquote><p>     2014-11-25 2014-12-05  1183     <h1>Aging Title</h1> 
   <a href="abc.com">
   </p></blockquote>
 3 <blockquote><p>     2014-11-25 2014-11-27  1184     <h1>Allergies Title</h1> 
   <a href="abc.com">
   </p></blockquote>

My query:

SELECT 
    st.TagContent, st.TagTitle              
FROM 
    Categories cpc 
INNER JOIN
    CSTagContent st ON st.CategoryID = cpc.CategoryID
WHERE 
    cpc.PostID = 581771 
    AND st.TagContent IS NOT NULL 
    AND st.TagContent <> ''                                               
    AND GETDATE() > st.StartDate  
    AND GETDATE() < DATEADD(dd, 1, st.EndDate)

Current output:

 TagContent             TagTitle

<blockquote><p>     
<a href="abc.com">    <h1>Aging Title</h1>
</p></blockquote>

<blockquote><p>      <h1>Allergies Title</h1> 
<a href="abc.com">
</p></blockquote>

In above output TagContent has same values for both rows, so i want it distinct and TagTitle should be appended or merged with '&' with the other row/rows as shown below...

Expected output:

   TagContent                    TagTitle

  <blockquote><p>     
  <a href="abc.com">    <h1>Aging Title</h1>&<h1>Allergies Title</h1> 
  </p></blockquote>

Thanks in advance..!

Upvotes: 0

Views: 405

Answers (1)

Sarath Subramanian
Sarath Subramanian

Reputation: 21301

SAMPLE TABLES

SELECT * INTO Categories
FROM
(
   SELECT 1148 CategoryId, 581771 PostId
   UNION ALL
   SELECT 1183 CategoryId, 581771 PostId
   UNION ALL
   SELECT 1184 CategoryId, 581771 PostId   
)TAB


SELECT * INTO TagContent
FROM
(
SELECT 1 [Id], '<blockquote><p><a href="abc.com"></p></blockquote>' TagContent ,    '2014-11-08' StartDate, '2014-11-14' EndDate,  1148 CategoryID, NULL TagTitle     

UNION ALL   
SELECT  2, '<blockquote><p><a href="abc.com"></p></blockquote>',     '2014-11-25', '2014-12-05',  1183,     '<h1>Aging Title</h1>' 


UNION ALL
SELECT  3, '<blockquote><p><a href="abc.com"></p></blockquote>',     '2014-11-25', '2014-11-27',  1184,     '<h1>Allergies Title</h1>' 
)TAB

Now we convert TagTitle to Ambersand seperated valuesfor the same TagContent. Since XML format is used we need to replace &gt, &lt and&ampto <, > and &.

QUERY

SELECT DISTINCT TagContent,STUFF(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(
        (SELECT '&' + TagTitle
        FROM TagContent T2 
        WHERE ST.TagContent=T2.TagContent 
        FOR XML PATH('')),2,200000),'&lt;','<'),'&gt;','>'),'&amp;','&'),'amp;',''),1,'') TagTitle
        FROM Categories CPC
        JOIN TagContent ST ON CPC.CategoryId=ST.CategoryId

Upvotes: 1

Related Questions