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
– Andomar, Commented Aug 19, 2012 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
– Graeme Marshall, Commented Aug 19, 2012 at 18:23
Please don't add code samples in a comment. Update the question! — Aaron Bertrand
– Aaron Bertrand, Commented Aug 19, 2012 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
– Andomar, Commented Aug 19, 2012 at 19:04

Taryn · Accepted Answer · 2012-08-19 23:51:43Z

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.

Collectives™ on Stack Overflow

sql join multiple tables, to return rows as columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related