Generate "scatter plot" result of members against sets from SQL query

Question

I have a staff database table containing staff members, with user_no and user_name columns. I have another, department, table containing the departments which staff can be members of, with dept_no and dept_name as columns.

Because staff can be members of multiple departments, I have a third, staff_dept, table with a user_no column and a dept_no column, which are the primary keys of those other two tables. This table shows which departments each member of staff belongs to and contains one row for each user/department intersection.

I would like to have an output in the form of a spreadsheet (CSV file, whatever; I'll be fine mangling the results into a usable form after I've got them) with one column for each department, and one row for each user, with an X appearing at each intersection, as defined in staff_dept.

Can I write a single SQL query which will achieve this result? or will I have to do some "real" programming (because it's not a "real" program until you've nested three or four for loops, obviously) to collect and format this data?

which rdbms? oracle? sql-server? mysql? postgre? db2? access? — mellamokb, Apr 26 '12 at 13:50

mellamokb · Accepted Answer · 2012-04-26T14:08:16.217

5

This can be done with a PIVOT table (using SQL Server):

SELECT user_name, [dept1name], [dept2name], [dept3name], ...
FROM
    (SELECT s.user_name, d.dept_name,
     case when sd.user_no is not null then 'X' else '' end as matches
     from staff s
     cross join department d
     left join staff_dept sd on s.user_no = sd.user_no and d.dept_no = sd.dept_no
    ) AS s
PIVOT
(
    min(matches)
    FOR dept_name IN ([dept1name], [dept2name], [dept3name], ...)
) AS pvt
order by user_name

Demo: http://www.sqlfiddle.com/#!3/c136d/5

Edit: To generate the PIVOT query dynamically from the list of departments in the table, you would make use of dynamic SQL, i.e., generate the code into a variable and use sp_executesql helper stored procedure. Here's an example: http://www.sqlfiddle.com/#!3/c136d/14

edited Apr 26 '12 at 14:08

answered Apr 26 '12 at 13:59

mellamokb

56,094
12
110
136

That is very cool. Is there a way to do it so that you don't have to type out all the department names as columns? A way to select the rows as columns? – Dan Apr 26 '12 at 14:03
@Dan: Yes, using dynamically-generated SQL code. I'll update with an example. – mellamokb Apr 26 '12 at 14:03
thanks. It's a bit over my head but I'll know what to look up if I run into a similar situation. Is the dynamic part recursive and the coalesce() just to handle the first case when @sql is still NULL or am I completely off? – Dan Apr 26 '12 at 14:28
2

@Dan: It is not recursive, per se. The `coalesce(@sql + ',', '')` is a common pattern to generate a comma-separated list. Without `coalesce`, there would be a comma either before every entry, or after every entry, so there would be one extra comma either way. With this method, I make sure a comma is *not* included the first time only, so that commas end up only between entries to get a true comma-separated list. – mellamokb Apr 26 '12 at 14:35

score 1 · Answer 2 · edited May 23 '17 at 11:49

In SQL Server (if you're using SQL Server), I would start with a full outer join (to include all staff and departments, not just those involved in the relation), drop that into a pivot statement to pivot all departments into columns, and then build a short script to generate and dynamically execute that SELECT statement (because the columns created by a pivot statement must be hard-coded, they can't be dynamically generated at run time).

Here's a sample -- it's an unpivot statement, but the concept is pretty much the same.

Generate "scatter plot" result of members against sets from SQL query

2 Answers2

Linked