sql join multiple tables, to return rows as columns

Question

apologies if this has been answered somewhere, I've had a search but can't find the answer.

I've got 3 SQL tables: employee table (ID, name, etc....) key skills table (id, skill name) link table (employee ID, skill ID)

An employee can obviously have multiple key skills, but I'm trying to report them in 1 row as an 'employee report', like this:

Row 1 - name, dob, key skill 1, key skill 2, etc.... Row 2 - name, dob, key skill 1, key skill 2, etc....

I can get the skills to return as a number of rows using:

SELECT DISTINCT kst.KeySkillName FROM KeySkillsTable
    INNER JOIN KeySkillsLinkTable kslt ON kslt.EmployeeId = 2
    INNER JOIN KeySkillsTable kst ON kst.Id = kslt.KeySkillsId

but when I put this into a larger select as a subquery I get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I presume this is because the subquery returns multiple rows, rather than multiple columns which I need.

Any help anyone can give would be greatly appreciated, thanks in advance.

A subquery in a scalar context can only return one row. It's hard to suggest a fix without seeing the query that causes the problem. So please elaborate on `when I put this into a larger select as a subquery`. — Andomar, Aug 19 '12 at 18:20
OK, here we go: SELECT FirstName, LastName, DOB, (SELECT DISTINCT kst.KeySkillName FROM KeySkillsTable INNER JOIN KeySkillsLinkTable kslt ON kslt.EmployeeId = emp.id INNER JOIN KeySkillsTable kst ON kst.Id = kslt.KeySkillsId) AS [Key Skill] FROM EmployeeTable emp WHERE emp.ID = 2 thanks — Graeme Marshall, Aug 19 '12 at 18:23
Please don't add code samples in a comment. Update the question! — Aaron Bertrand, Aug 19 '12 at 18:59
Ok, so what do you expect SQL Server to do when the subquery contains two rows? It can't fit two rows into one column! — Andomar, Aug 19 '12 at 19:04
If you truly need to have skills across multiple columns. In what order should they appear and how many could there be? Your reporting tool will probably be able to pivot the rows. I have done this with SSRS in fact. — shawnt00, Aug 19 '12 at 20:29
It seems like people are doing this using *XML PATH*. Here for example: http://stackoverflow.com/a/545672/1001985 — McGarnagle, Aug 19 '12 at 21:33

score 4 · Answer 1 · answered Aug 19 '12 at 23:51

You can perform this type of transformation with a PIVOT. There are two ways, a static pivot where you hard-code the values of the columns or a dynamic pivot where the columns are determined at run-time. Here is an example of how you can do this for your situation.

Static Pivot (See SQL Fiddle with Demo)

select *
from 
(
  select e.id, e.name, s.skillname, count(*) cnt
  from employee e
  left join emp_skill es
    on e.id = es.emp_id
  left join skills s
    on es.skill_id = s.id
  group by e.id, e.name, s.skillname
) x
pivot
(
  sum(cnt)
  for skillname in([skill 1], [skill 2], [skill 3])
) p

Dynamic Pivot (See SQL Fiddle with Demo)

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(skillname) 
                    from skills
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
      = 'SELECT id, name,' + @cols + ' from 
         (
            select e.id, e.name, s.skillname, count(*) cnt
            from employee e
            left join emp_skill es
              on e.id = es.emp_id
            left join skills s
              on es.skill_id = s.id
            group by e.id, e.name, s.skillname
         ) x
         pivot 
         (
            sum(cnt)
            for skillname in(' + @cols + ')
         ) p '

execute(@query)

Both queries will produce the same results. The difference is that in the first you have to code all of the values that you want to become columns.

sql join multiple tables, to return rows as columns

1 Answers1