Column divided into sub columns in sql server

Question

I have created a table like this with some data.

DROP TABLE IF EXISTS #EmployeeDetail
CREATE TABLE #EmployeeDetail(Name nvarchar(50),Department nvarchar(50),IsActive INT)
INSERT INTO #EmployeeDetail(Name,Department,IsActive)
VALUES ( 'John','Account', 1 ), ( 'Harry', 'Store', 0), ( 'Smile', 'HR', 1);

The output of the query is

Name	Department	IsActive
John	Account	1
Harry	Store	0
Smile	HR	1

Then I did pivoting where the row becomes column by using this query

DECLARE 
    @columns NVARCHAR(MAX) = '', 
    @sql     NVARCHAR(MAX) = '';

 

    
DROP TABLE IF EXISTS #EmployeeDetail
CREATE TABLE #EmployeeDetail(Name nvarchar(50),Department nvarchar(50),IsActive INT)
INSERT INTO #EmployeeDetail(Name,Department,IsActive)
VALUES ( 'John','Account', 1 ), ( 'Harry', 'Store', 0), ( 'Smile', 'HR', 1);

 

SELECT 
    @columns+=QUOTENAME(Department) + ','
FROM    #EmployeeDetail;

 

    SET @columns = LEFT(@columns, LEN(@columns) - 1);

 

    DECLARE @GrandTotalCol    NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + 
CAST (Department AS VARCHAR) +'],0) + ', 'ISNULL([' + CAST(Department AS VARCHAR)+ '],0) + ')
FROM     #EmployeeDetail
  GROUP BY Department
  ORDER BY Department;
 SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1);
SET @sql ='
SELECT *, (' + @GrandTotalCol + ') 
AS [Grand Total] INTO  #temp_MatchesTotal FROM   
(
    SELECT 
        Name, 
        Department
        
    FROM #EmployeeDetail
) t 
PIVOT(
  COUNT(Department) 
    FOR Department IN ('+ @columns +')
)

 

 AS pivot_table
 SELECT * FROM  #temp_MatchesTotal
  DROP TABLE  #temp_MatchesTotal
 
 ;';

 

EXECUTE sp_executesql @sql;

which gives the output like this:

Name	Account	Store	HR	Grand Total
John	0	1	0	1
Harry	1	0	0	1
Smile	0	0	1	1

Now it seems to be pretty well until this, but now i want to extract a table in a report like this:

Is there any way to achieve these type of thing through query ? So that i could easily map the extracted data into the report. Here the pivoted column is also filtered according to IsActive column in the table #EmployeeDetail.

Answer 1

You can get something similar to the desired result. I used CASE statements to transpose the result:

You will need two CASE statement for each department (one for IsActive and another for IsNotActive).

SELECT 
      Name,
      SUM(CASE WHEN (Department = 'Account' AND IsActive = 1) THEN 1 ELSE 0 END) AS AccountIsActive,
      SUM(CASE WHEN (Department = 'Account' AND IsActive = 0) THEN 1 ELSE 0 END) AS AccountIsNotActive,
      
      SUM(CASE WHEN (Department = 'Store' AND IsActive = 1) THEN 1 ELSE 0 END) AS StoreIsActive,
      SUM(CASE WHEN (Department = 'Store' AND IsActive = 0) THEN 1 ELSE 0 END) AS StoreIsNotActive,
      
      SUM(CASE WHEN (Department = 'HR' AND IsActive = 1) THEN 1 ELSE 0 END) AS HRIsActive,
      SUM(CASE WHEN (Department = 'HR' AND IsActive = 0) THEN 1 ELSE 0 END) AS HRIsNotActive,
      
      COUNT(IsActive) AS GrandTotal
      
FROM #EmployeeDetail
GROUP BY Name;

Output:

Name	AccountIsActive	AccountIsNotActive	StoreIsActive	StoreIsNotActive	HRIsActive	HRIsNotActive	GrandTotal
Harry	0	0	0	1	0	0	1
John	1	0	0	0	0	0	1
Smile	0	0	0	0	1	0	1

Dynamic query

SELECT STRING_AGG(query_piece, '')

FROM (

    (SELECT 'SELECT Name,' AS query_piece)

    UNION ALL

    (SELECT 
        CONCAT('SUM(CASE WHEN Department = ''', Department, ''' AND IsActive = ', IsActive,
            ' THEN 1 ELSE 0 END) AS ', Department, '_', CASE WHEN IsActive = 1 THEN 'IsActive' ELSE 'IsNotActive' END, ',')
    FROM (SELECT DISTINCT Department FROM #EmployeeDetail) AS sqD
    CROSS JOIN (SELECT DISTINCT IsActive FROM #EmployeeDetail) AS sqIA)

    UNION ALL

    (SELECT 'COUNT(IsActive) AS GrandTotal
         FROM #EmployeeDetail
         GROUP BY Name;')) AS dynamic_query;

Dynamic query output is your requested query:

SELECT Name,
    SUM(CASE WHEN Department = 'Account' AND IsActive = 0 THEN 1 ELSE 0 END) AS Account_IsNotActive,
    SUM(CASE WHEN Department = 'HR' AND IsActive = 0 THEN 1 ELSE 0 END) AS HR_IsNotActive,
    SUM(CASE WHEN Department = 'Store' AND IsActive = 0 THEN 1 ELSE 0 END) AS Store_IsNotActive,
    SUM(CASE WHEN Department = 'Account' AND IsActive = 1 THEN 1 ELSE 0 END) AS Account_IsActive,
    SUM(CASE WHEN Department = 'HR' AND IsActive = 1 THEN 1 ELSE 0 END) AS HR_IsActive,
    SUM(CASE WHEN Department = 'Store' AND IsActive = 1 THEN 1 ELSE 0 END) AS Store_IsActive,
    COUNT(IsActive) AS GrandTotal
FROM #EmployeeDetail
GROUP BY Name;