Столбец разделен на подстолбцы в сервере sql

Я создал такую ​​таблицу с некоторыми данными.

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);

Результат запроса

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

Затем я сделал поворот, где строка становится столбцом, используя этот запрос

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;

который дает вывод следующим образом:

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

Теперь, кажется, все было хорошо до этого, но теперь я хочу извлечь таблицу в такой отчет: введите здесь описание изображения

Есть ли способ добиться такого типа вещей с помощью запроса? Чтобы я мог легко отображать извлеченные данные в отчет. Здесь сводной столбец также фильтруется по столбцу IsActive в таблице #EmployeeDetail.


sql sql-server sql-server-2012-express
person Y B    schedule 26.07.2021    source источник
comment
Вы не можете иметь несколько слоев заголовков в SQL-запросе, нет. Это что-то для вашего слоя отчетности/презентации. Я бы посоветовал вам сделать (динамический) поворот там. Возможно используйте матрицу в SSRS.   -  person Larnu    schedule 26.07.2021
comment
@Larnu, как я могу извлечь данные с помощью запроса, чтобы эти данные можно было легко сопоставить в этих отчетах с этими таблицами?   -  person Y B    schedule 26.07.2021
comment
Просто выберите все свои данные и используйте любые инструменты поворота, которые использует ваше программное обеспечение для создания отчетов. SQL не создан для такого поворота   -  person Charlieface    schedule 26.07.2021

Ответы (1)


arrow_upward
0
arrow_downward

Вы можете получить что-то похожее на желаемый результат. Я использовал операторы CASE для переноса результата:

Вам понадобится два оператора CASE для каждого отдела (один для IsActive и другой для 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;

Выход:

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

Динамический запрос

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;

Вывод динамического запроса - это ваш запрошенный запрос:

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;
person nachospiu    schedule 26.07.2021
comment
Это статический подход, но поля в разделе могут не совпадать каждый раз. Поэтому нужен динамичный подход. - person Y B; 27.07.2021
comment
Вы можете проверить этот ответ, чтобы сделать динамический запрос, или адаптировать исходный запрос, используя этот подход. - person nachospiu; 27.07.2021