SQL CONCAT IF语句？ [英] SQL CONCAT IF Statement?

问题描述

Morning All，

Morning All,

我不确定我需要如何解决我的以下查询...我有以下查询，它在SQL服务器中拉回所需的记录...

Im not to sure how i need to solve my following query... I have the following query which pulls back the desired records in SQL server...

SELECT agenda.AgendaItemNumber,Agenda.AgendaName, AgendaType.AgendaTypeDescription, userdetails.fullName 
FROM Agenda
JOIN AgendaType ON AgendaType.AgendaTypeID=Agenda.AgendaTypeID
JOIN UserDetails ON Agenda.AgendaID = Userdetails.AgendaID
WHERE agenda.AgendaTypeID = '2'
AND AgendaItemNumber = AgendaItemNumber
AND AgendaName = AgendaName
AND AgendaTypeDescription = AgendaTypeDescription
AND AgendaItemNumber >= '3'

以上查询有效，但我需要稍微增强一点。它会回退以下结果，这些结果基本上是重复记录，除了'fullname'列...

The above query works but i need to enhance this slightly. It pulls back the following results, which essentially are duplicate records except for the 'fullname' column...

我想做的是能够为这个查询添加一些额外的代码，这样当我运行查询我能够为每个'AgendaItemNumber'显示一条记录，并使其连接该记录的两个完整名称。但是我在这个表中有额外的AgendaItemsNumbers只分配了1个用户全名。它只是图像文件中的这几条记录，我需要做一些聪明的事情。

What i would like to do is be able to add some extra code to this query so that when i run the query i am able to display one record for each 'AgendaItemNumber' and for it to concat both of the fullnames for this record. However i have additional AgendaItemsNumbers in this table that only have 1 x user fullname assigned to them. its just these few records within the image file i need to do something clever with.

也许有更好的方法来完成这项任务？

Maybe there is a better way to complete this task?

非常感谢提前。任何疑问请不要犹豫。

Many thanks in advance. Any queries please dont hesitate to ask.

问候
贝蒂

推荐答案

SELECT  agenda.AgendaItemNumber,
        Agenda.AgendaName, 
        AgendaType.AgendaTypeDescription, 
        STUFF(( SELECT  ';' + FullName 
                FROM    UserDetails
                WHERE   UserDetails.AgendaID = Agenda.AgendaID
                FOR XML PATH('')
            ), 1, 1, '') AS fullName 
FROM    Agenda
        INNER JOIN AgendaType 
            ON AgendaType.AgendaTypeID=Agenda.AgendaTypeID
        INNER JOIN UserDetails 
            ON Agenda.AgendaID = Userdetails.AgendaID
WHERE   agenda.AgendaTypeID = '2'
AND     AgendaItemNumber = AgendaItemNumber
AND     AgendaName = AgendaName
AND     AgendaTypeDescription = AgendaTypeDescription
AND     AgendaItemNumber >= '3'

ADENDUM

SQL-Server中的XML扩展允许您将多行连接成一行。扩展的实际意图是你可以输出为XML（显然），但有一些漂亮的技巧是扩展的副产品。在上面的查询中，如果子查询（FullName）中有列名，它将输出为< FullName> Joe Bloggs1< / FullName>< FullName> Joe Bloggs2< / FullName> ，因为没有列名，它只是简单地连接行（没有形成正确的XML）。 PATH 部分允许您指定其他节点，例如，如果您在上面使用PATH（'Name'），您将获得< Name> ;; Joe Bloggs< / Name>< Name> ;; Joe Bloggs2< / Name> 如果您将Path与列名组合，您将获得Joe Bloggs。

The XML extension in SQL-Server allows you to concatenate multiple rows into a single row. The actual intention of the extension is so you can output as XML (obviously), but there are some nifty tricks that are byproducts of the extensions. In the above query, if there were a column name in the subquery (FullName) it would output as <FullName>Joe Bloggs1</FullName><FullName>Joe Bloggs2</FullName>, because there is no column name it simply concatenates the rows (not forming proper XML). The PATH part allows you to specify an additional node, for example if you use PATH('Name') in the above you would get <Name>;Joe Bloggs</Name><Name>;Joe Bloggs2</Name> If you combine Path with a column name you would get Joe Bloggs.

最后 STUFF 只删除列表开头的分号。

Finally the STUFF just removes the semicolon at the start of the list.

这篇关于SQL CONCAT IF语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！