TSQL Pivoting Issue - looking for better approach

问题:

This is a T-SQL related question. I am using SQL Server 2012.

I have a table like this:

I would like to have output like this:

Explanation:

For each employee, there will be a row. An employee has one or more assignments. Batch Id specifies this. Based on the batch Id, the column names will change (e.g. Country 1, Country 2 etc.).

Approach so far:

Un-pivot the source table like the following:

select 
    EmpId, 'Country ' + cast(BatchId as varchar) as [ColumnName], 
    Country as [ColumnValue] 
from 
    SourceTable
UNION
select 
    EmpId, 'Pass ' + cast(BatchId as varchar) as [ColumnName], 
    Pass as [ColumnValue] 
from 
    SourceTable

which gives each column's values as rows. Then, this result can be pivoted to get the desired output.

Questions:

• Is there a better way of doing this?
• At the moment, I know there will be fixed amount of batches, but, for future, if I like to make the pivoting part dynamic, what is the best approach?
• Using tools like SSIS or SSRS, is it easier to handle the pivot dynamically?

回答1:

Screw doing it in SQL.

Let SSRS do the work for you with a MATRIX. It will PIVOT for you without having to create dynamic SQL to handle the terrible limitation of needing to know all the columns.

For your data, you would have EMP ID as the ROW Group and PASS as your column grouping.

https://msdn.microsoft.com/en-us/library/dd207149.aspx

回答2:

There are many possible solutions to achieve what you want (search for Dynamic Pivot on multiple columns)

SqlFiddleDemo

Warning: I assume that columns Country and Pass are NOT NULL

CREATE TABLE SourceTable(EmpId INT, BatchId INT,
             Country NVARCHAR(100) NOT NULL, Pass NVARCHAR(5) NOT NULL);

INSERT INTO SourceTable(EmpId, BatchId, Country, Pass)
VALUES 
(100, 1, 'UK', 'M'),        (200, 2, 'USA', 'U'),
(100, 2, 'Romania', 'M'),   (100, 3, 'India', 'MA'),
(100, 4, 'Hongkong', 'MA'), (300, 1, 'Belgium', 'U'),
(300, 2, 'Poland', 'U'),    (200, 1, 'Australia', 'M');


/* Get Number of Columns Groups Country1..Country<MaxCount> */
DECLARE @max_count INT
       ,@sql      NVARCHAR(MAX) = ''
       ,@columns  NVARCHAR(MAX) = ''
       ,@i        INT           = 0
       ,@i_s       NVARCHAR(10);

WITH cte AS
(
  SELECT EmpId
        ,[cnt] = COUNT(*)
  FROM SourceTable
  GROUP BY EmpId
)
SELECT @max_count = MAX(cnt)
FROM cte;

WHILE @i < @max_count
BEGIN
    SET @i += 1;
    SET @i_s = CAST(@i AS NVARCHAR(10));
    SET @columns += N',MAX(CASE WHEN [row_no] = ' + @i_s + ' THEN Country END) AS Country' + @i_s +
                     ',MAX(CASE WHEN [row_no] = ' + @i_s + ' THEN Pass END) AS Pass' + @i_s;   
END 

SELECT @sql =
   N';WITH cte AS (
   SELECT EmpId, Country, Pass, [row_no] = ROW_NUMBER() OVER (PARTITION BY EmpId ORDER BY BatchId)
   FROM SourceTable)
   SELECT EmpId ' +  @columns + N'
   FROM cte
   GROUP BY EmpId';

/* Debug */
/* SELECT @sql */

EXEC(@sql);

Or:

SQLFiddleDemo2

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

;WITH cte(col_name, rn) AS(
SELECT DISTINCT col_name = col_name + CAST(BatchId AS VARCHAR(10)),
       rn = ROW_NUMBER() OVER(PARTITION BY EmpId ORDER BY BatchId)
FROM SourceTable
CROSS APPLY (VALUES ('Country', Country), ('Pass', Pass)) AS c(col_name, val)
)
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(col_name)
                      FROM cte
                      ORDER BY rn /* If column order is important for you */
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SET @sql =
 N';WITH cte AS 
   (
       SELECT EmpId, col_name = col_name + CAST(BatchId AS VARCHAR(10)), val
       FROM SourceTable
       CROSS APPLY (VALUES (''Country'', Country), (''Pass'', Pass)) AS c(col_name, val)
   )  
   SELECT *
   FROM cte
   PIVOT
   (
      MAX(val)
      FOR col_name IN (' + @cols + ')
    ) piv';

EXEC(@sql);