SQL CONCAT IF Statement?

问题:

Morning All,

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'

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

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.

Regards Betty

回答1:

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

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.

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