Concatenate fields from SQL query using ColdFusion

Question

I'm working with a table that has a structure like this:

ID
UserID
Team1
Team2
Team3
Team4

It's a screwed-up structure, I know, restructuring as two tables in a one-to-many relationship would be better, but it's what I have to work with.

I need to output a list of teams with their associated users. The problem is I have data like this:

User: Joe    Team1: Aces
User: Jill   Team1: Betas
User: Kim    Team2: Aces
User: Skip   Team3: Deltas
User: Zed    Team1: Betas
User: Joe    Team2: Deltas

What I want to output would be something like this:

Aces: Joe
Aces: Kim
Betas: Jill
Betas: Zed
Deltas: Skip
Deltas: Joe

My current plan is to convert the query results to arrays (I'm using ColdFusion 8 on the server), join them, and then sort for teams. However, I wondered if there is a simpler, perhaps SQL oriented approach I'm missing. Anyone spot a better way?

Answer 1

A UNION of multiple queries, given the structure of your table and the data you have to work with, may be a good solution for you:

( SELECT Team1 as Team, UserName
FROM teamData as td1
WHERE td1.Team1 IS NOT NULL
AND td1.Team1 != '' )
UNION
( SELECT Team2 as Team, UserName
FROM teamData as td2
WHERE td2.Team2 IS NOT NULL
AND td2.Team2 != '' )
UNION
( SELECT Team3 as Team, UserName
FROM teamData as td3
WHERE td3.Team3 IS NOT NULL
AND td3.Team3 != '' )
UNION
( SELECT Team4 as Team, UserName
FROM teamData as td4
WHERE td4.Team4 IS NOT NULL
AND td4.Team4 != '' )
ORDER BY Team, UserName;

Which gives you:

Team    UserName
Aces    Joe
Aces    Kim
Betas   Jill
Betas   Zed
Deltas  Skip

Working SQLFiddle is here for testing (this was written against a MySQL db):

http://sqlfiddle.com/#!9/8e7651/30

Answer 2

If possible, I would go with a view to normalize your data. Then just query the view. To get the data you need, use an UNPIVOT (if SQL Server, then 2008 or later).

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE users ( 
    userid int
  , username varchar(20)
) ;

INSERT INTO users (userid, username)
VALUES (1,'Joe')
  , (2,'Jill')
  , (3,'Kim')
  , (4,'Skip')
  , (5,'Zed')
;

CREATE TABLE teams ( 
    id int identity
  , userid int
  , Team1 varchar(10)
  , Team2 varchar(10)
  , Team3 varchar(10)
  , Team4 varchar(10)
) ;

INSERT INTO teams (userid, Team1, Team2, Team3, Team4)
VALUES 
    ( 1,'Aces',null,null,null )
  , ( 2,'Betas',null,null,null )
  , ( 3,null,'Aces',null,null )
  , ( 4,'Aces',null,'Deltas',null )
  , ( 5,'Betas',null,null,'Deltas' )
  , ( 6,'Aces','Betas','Nobody','Deltas' )
;

I added an extra test row into the data to show how to handle a row in Team with a missing UserID.

Query 1:

SELECT t3.Team, t3.username
FROM (
  SELECT t2.userid, t2.username, t2.Team
  FROM (
    SELECT u.userid, u.username, t.Team1, t.Team2, t.Team3, t.Team4
    FROM teams t
    LEFT OUTER JOIN users u ON t.userid = u.userid
  ) ut
  UNPIVOT
  ( Team FOR Teams IN (Team1, Team2, Team3, Team4 ) ) t2 
  WHERE t2.userid IS NOT NULL
) t3
ORDER BY Team, username

NOTE: The table aliases show where the final pieces of data are coming from.

Results:

|   Team | username |
|--------|----------|
|   Aces |      Joe |
|   Aces |      Kim |
|   Aces |     Skip |
|  Betas |     Jill |
|  Betas |      Zed |
| Deltas |     Skip |
| Deltas |      Zed |

I believe there's also a way to do this in MySQL and Oracle (or any other database that can do UNPIVOT behavior), but I don't know the syntax right now. If you're stuck on SQL 2005 or lower, it can be done, but it's a lot more complicated.

---

If you make the query a SQL VIEW, you just cfquery the view and then output those results.

<cfquery name="foo" datasource="#myDSN#">
    SELECT Team, username
    FROM vw_teamUsers (or whatever you name it)
</cfquery>

<cfoutput query="foo">
    #Team#: #username# <br>
</cfoutput>