使用 XML 标记将行转换为逗号分隔值 [英] Rows to comma separated values using XML tag

问题描述

我有 2 个名为Categories"和CSTagContent"的表，下面显示了数据...

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

表 1:类别"

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

表 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>

我的查询:

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)

当前输出:

 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>

在上面的输出中 TagContent 对两行都有相同的值，所以我希望它是不同的，TagTitle 应该附加或与 '&' 合并与其他行/行如下所示...

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...

预期输出:

   TagContent                    TagTitle

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

提前致谢..！

推荐答案

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

现在我们将 TagTitle 转换为相同 TagContent 的 Ambers 和分隔值.由于使用了 XML 格式，我们需要将 &gt、&lt 和 &amp 替换为 <、>和 &.

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 &.

查询

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

• SQL FIDDLE

这篇关于使用 XML 标记将行转换为逗号分隔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！