2

Given a table like this:

[Last Name] [First Name] [DepartmentID]
---------------------------------------
Doe         John         1
Doe         John         2
Black       Frank        3

, I'd like a result like this:

[Last Name] [First Name] [Accounting] [Management] [Development]
----------------------------------------------------------------
Doe         John         X            X
Black       Frank                                  X

Thus far, I have a query like this:

SELECT [Last Name], [First Name], [1], [2], [3]
FROM Employees
PIVOT(SUM(DepartmentID) FOR DepartmentID IN ([1], [2], [3])
GROUP BY [Last Name], [First Name], [1], [2], [3]

Which gives me:

[Last Name] [First Name] [1] [2] [3]
----------------------------------------------------------------
Doe         John         1
Doe         John             2
Black       Frank                3

Several problems:

  • the column name comes from elsewhere, i.e. it should be derived from a function or an subquery. Can this be done?
  • I assume I'll have to use dynamic SQL to get the possible departments, i.e. I can't tell PIVOT to simply determine all possibilities?
  • the column values should be just 'X' (or similar), not contain the real value. There shouldn't be multiple lines per person per possible department, which means I can't group by the pivoted columns. How do I get one line per person?

I don't really want or need to aggregate anything; I just wish to show, per department, whether the employee resides in that department. Is PIVOT a red herring for this? I could possibly solve this in a more complicated manner with an EXISTS statement per department?

Arion
  • 31,011
  • 10
  • 70
  • 88
Sören Kuklau
  • 19,454
  • 7
  • 52
  • 86
  • 1
    You should delete the `GROUP BY [Last Name], [First Name], [1], [2], [3]` bit. It is pointless. BTW you must have an additional column in your table you haven't shown us like `RecordId` that is causing the multiple rows issue. [Only 1 row per Employee returned here](http://data.stackexchange.com/stackoverflow/query/60760/new) – Martin Smith Feb 03 '12 at 17:57
  • There are indeed additional columns, which may differ from each other. You're also correct that the PIVOT appears to imply a GROUP BY. – Sören Kuklau Feb 03 '12 at 18:21
  • The way around that is to use a CTE or derived table as the PIVOT source with only the 3 columns of interest. You can use a `CASE` statement to get your `X` rather than the number. You need dynamic SQL if the column list is dynamic. – Martin Smith Feb 03 '12 at 18:24

1 Answers1

10

Maybe this will help:

First some test data:

CREATE TABLE tbl(LastName VARCHAR(100),FirstName VARCHAR(100),DepartmentID INT)
CREATE TABLE tblDepartment(DepartmentID INT,Name VARCHAR(100))

INSERT INTO tbl
SELECT 'Doe','John',1 UNION ALL
SELECT 'Doe','John',2 UNION ALL
SELECT 'Black','Frank',3

INSERT INTO tblDepartment
SELECT 1,'Accounting' UNION ALL
SELECT 2,'Management' UNION ALL
SELECT 3,'Development'

The concating the columns:

DECLARE @cols VARCHAR(MAX)
SELECT  @cols = COALESCE(@cols + ','+QUOTENAME(Name),
                     QUOTENAME(Name))
FROM 
    tblDepartment
ORDER BY 
    Name

Or you might want to concat the columns with just the existing ones like this:

DECLARE @cols VARCHAR(MAX)
SELECT  @cols = COALESCE(@cols + ','+QUOTENAME(Name),
                     QUOTENAME(Name))
FROM 
    tblDepartment
WHERE EXISTS
        (
            SELECT 
                NULL 
            FROM 
                tbl AS tbl2 
            WHERE 
                tblDepartment.DepartmentID=tbl2.DepartmentID) 
ORDER BY 
    Name

Then execute the dynamic sql:

DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
    SELECT
        tbl.LastName,
        tbl.FirstName,
        Department.Name,
        ''X'' as test
    FROM
        tbl AS tbl
        JOIN tblDepartment AS Department
            ON Department.DepartmentID=tbl.DepartmentID
)AS p
PIVOT
(
    MAX(test) FOR Name IN ('+@cols+')
) As Pvt'

EXECUTE(@query)

And in my case dropping the created tables:

DROP TABLE tbl
DROP TABLE tblDepartment
Arion
  • 31,011
  • 10
  • 70
  • 88