SQL CONCAT IF Statement?

Question

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

Results_image

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

**AND AgendaItemNumber = AgendaItemNumber AND AgendaName = AgendaName AND AgendaTypeDescription = AgendaTypeDescription** does not make sense. Also use **INNER JOIN** — Kaf, Mar 12 '12 at 11:35

GarethD · Accepted Answer · 2012-03-12T12:12:48.503

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.

@GarethD Many thanks for the above query. This smmems to work a treat. May i just ask what the XML Path does? Is the essentially the CONCAT part of the query for the FullName column. Not too sure what the.... ,1,1 '') part of the query does. However this does the trick. Many Thanks. — Betty, Mar 12 '12 at 12:00
@Betty The `STUFF(..1, 1, '')` Part just removes the first semicolon so you get "Name1;Name2" instead of ";Name1;Name2". You could feasibly just use `FullName + ';'` and remove the `STUFF` completely to get a list like this "Name1;Name2;" — GarethD, Mar 12 '12 at 12:16

SQL CONCAT IF Statement?

1 Answers1

Linked