CODEWITHSUNDEEP
sqlsql-servercoldfusioncoldfusion-10
rrk
rrk

Reputation: 15846

Combining multiple rows by stuffing columns into list

I have four tables employees, associations_employees, associations, association_items. The select query below yields me the joined rows.

Note: I have made tags for SQL as well as Coldfusion Language, this is because I am using coldfusion as my scripting language. I am not sure whether I should rely on SQL or use my scripting language.

Query

SELECT  AE.userid, E.firstname,
    A.title, AI.itemvalue
FROM  associations_employees AE
    INNER JOIN employees E on E.userid = AE.useridFK
    INNER JOIN associations A on A.associationid = AE.associationidFK
    INNER JOIN association_items AI on AI.associationidFK = AE.associationidFK

Current select output

userID  firstname  title           itemvalue
------  ---------  -----           ---------
5603    Jesh       Learner Type    Family Literacy   
5603    Jesh       Learner Type    Elementary School 
5603    Jesh       Learner Type    Academic             
5603    Jesh       Personnel Type  Staff             
5605    jennone    Personnel Type  Site Supervisor   
5605    jennone    Personnel Type  Rops member       
5607    Sharon     Personnel Type  Rops member       
5607    Sharon     Personnel Type  Site Supervisor
5607    Sharon     Mentor Type     High School
5607    Sharon     Mentor Type     Op. Read
5607    Sharon     Mentor Type     Enrichment
5607    Sharon     Mentor Type     General

As you can notice, there are multiple rows which are similar apart from 'itemvalue' column. I need to combine these rows to produce the following result.

Needed output

userID  firstname  title           itemvalue
------  ---------  ------          ---------
5603    Jesh       Learner Type    Family Literacy;Elementary School;Academic
5603    Jesh       Personnel Type  Staff
5605    jennone    Personnel Type  Rops member;Site Supervisor;Staff
5607    Sharon     Personnel Type  Rops member;Site Supervisor
5607    Sharon     Mentor Type     Enrichment;General;High School;Op. Read

Upvotes: -1

Views: 664

Answers (3)

James A Mohler
James A Mohler

Reputation: 11120

You could use something similar to Dan's approach with arrays. Array's might be faster

<cfoutput query="yourquery" group = "userid">
  <cfoutput group = "title">
    #userid # #firstname# #lastname# #title#
    <cfset items = []>
    <cfoutput>
        <cfset ArrayAppend(items, itemvalue)>
    </cfoutput>
    #ArrayToList(items, ";")#
  </cfoutput>
</cfoutput>

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20794

If you want a ColdFusion solution, the group attribute of cfoutput will work. Step 1 is to add an order by clause to your query.

order by userid, title

Next, your cfoutput tags.

<cfoutput query="yourquery" group = "userid">
  <cfoutput group = "title">
    #userid # #firstname# #lastname# #title#
    <cfset items = ''>
    <cfoutput>
      <cfset items = listappend(items,itemvalue,';')>
    </cfoutput>
    #items#
  </cfoutput>
</cfoutput>

This is the basic approach. You'll have to add formatting as well as figuring out how to exclude a trailing semi-colon from the itemvalue list.

Upvotes: 3

Tanner
Tanner

Reputation: 22733

You can use the STUFF method to achieve this:

SELECT AE.userid,
       E.firstname,
       A.title,
       STUFF((SELECT ',' + [AI.itemvalue]
              FROM association_items AI
              WHERE AI.associationidFK = AE.associationidFK
              FOR XML PATH('')), 1, 1, '') AS itemvalue
FROM associations_employees AE
INNER JOIN employees E ON E.userid = AE.useridFK
INNER JOIN associations A ON A.associationid = AE.associationidFK
GROUP BY AE.userid, E.firstname, A.title,

This has't been tested so may require some fine tuning.

Upvotes: 4

Related Questions