SQL Server Pivot/Unpivot an entire table (100+ fields)

Unfortunately, I'm stuck with this project the way it is. What I need to do is take a table with about 100 fields and compare two records, determining which fields don't match.

不幸的是，我坚持这个项目的方式。我需要做的是拿一个包含大约100个字段的表并比较两个记录，确定哪些字段不匹配。

The best way I could think of was to pivot them, so this:

我能想到的最好的方法是转动它们，所以这个：

KeyID    Field1    Field2   Field3    Field4   Field5    Field6
42       Apples    Pears    Blue      Hills    Dog       iPhone
65       Apples    Plums    Red       Hills    Cat       iPhone   

becomes this:

成为这个：

FieldName    KeyID_42    KeyID_65
Field1       Apples      Apples
Field2       Pears       Plums
Field3       Blue        Red
Field4       Hills       Hills
Field5       Dog         Cat
Field6       iPhone      iPhone

And then I can run a query against that like:

然后我可以运行一个查询，如：

SELECT *
FROM MyPivot
WHERE KeyID_42 <> KeyID_65

and I should be left with this:

我应该留下这个：

FieldName    KeyID_42    KeyID_65
Field2       Pears       Plums
Field3       Blue        Red
Field5       Dog         Cat

However, with a table with 100 or so fields, listing every single one of them in a PIVOT statement isn't gonna be pretty. And I may have to do this with another table later.

但是，对于包含100个左右字段的表，在PIVOT语句中列出它们中的每一个都不会很漂亮。我可能不得不在以后的另一张桌子上这样做。

Is there an easy way to achieve this? One that doesn't require listing each field separately?

有没有一种简单的方法来实现这一目标？一个不需要单独列出每个字段？

EDIT:

I'm at this point, but getting many errors like this: Invalid column name 'UpdateTime'.

我就是这样，但是遇到了很多这样的错误：列名“UpdateTime”无效。

The code I'm using is this:

我正在使用的代码是这样的：

DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

select @colsUnpivot 
  = stuff((select ','+quotename(C.name)
           FROM sys.columns c
           WHERE c.object_id = OBJECT_ID('dbo.tblSQLAdminInventory')
           --AND C.Name <> 'EffectiveDate' 
           for xml path('')), 1, 1, '')

set @query 
  = 'select KeyID, ID1, ID2
     from tblSQLAdminInventory
     unpivot
     (
        KeyID
        for ID1 in ('+ @colsunpivot +')
     ) u
     unpivot
     (
        KeyID
        for ID2 in ('+ @colsunpivot +')
     ) u     
     '

exec sp_executesql @query;

2 个解决方案

#1

Unpivot only once, and then write as follows:

只旋转一次，然后写如下：

;WITH cte AS (
    -- the query with unpivot but only once 
    -- UNPIVOT(value FOR ID IN (...))
)
SELECT
    *
FROM
    cte AS c1
    INNER JOIN cte AS c2 ON
        c2.ID=c1.ID
WHERE
    c1.KeyId=42 AND
    c2.KeyId=65 AND
    c1.value<>c2.value;

#2

This should do the trick, as it is dynamic you do not need to worry about the number of the fields:

这应该可以解决问题，因为它是动态的，你不需要担心字段的数量：

Assumption: The column names will always be in the format Field

假设：列名称将始终采用字段格式

Sample data:

样本数据：

CREATE TABLE temp2 (KeyID INT, Field1 VARCHAR(20), Field2 VARCHAR(20), Field3 VARCHAR(20), Field4 VARCHAR(20), Field5 VARCHAR(20), Field6 VARCHAR(20));

INSERT INTO temp2
VALUES
       (42, 'Apples', 'Pears', 'Blue', 'Hills', 'Dog', 'iPhone'),
       (65, 'Apples', 'Plums', 'Red', 'Hills', 'Cat', 'iPhone');

Query:

查询：

-- Where clause params
DECLARE @whereClauseParam VARCHAR(MAX) = '[42] <> [65]' --<-- User will need to determine what the condition will be

--Get the Fields required for the initial pivot
DECLARE @Fields VARCHAR(MAX)= '';

SELECT @Fields+=QUOTENAME(t.name)+', '
FROM sys.columns AS t
WHERE t.object_id = OBJECT_ID('temp2')
      AND t.name LIKE 'Field[0-9]%';

-- Get the KeyId's with alias added
DECLARE @keyIDs VARCHAR(MAX)= '';

SELECT @keyIDs+=QUOTENAME(t.KeyID)+' AS [KeyID_'+CAST(t.KeyID AS VARCHAR(10))+'], '
FROM temp2 AS t;

-- Get the KeyId's without alias
DECLARE @keyIDs1 VARCHAR(MAX)= '';

SELECT @keyIDs1+=QUOTENAME(t.KeyID)+', '
FROM temp2 AS t;

--Generate Dynamic SQL
DECLARE @SQL2 VARCHAR(MAX)= 'SELECT  Value AS FieldName,';

SELECT @SQL2+=SUBSTRING(@keyIDs, 1, LEN(@keyIDs)-1)+'
FROM
(SELECT KeyID , Value , FieldName
FROM 
   (SELECT KeyID,'+SUBSTRING(@Fields, 1, LEN(@Fields)-1)+'
   FROM temp2) p --<-- you will need to change the name to point to your table
UNPIVOT
   (FieldName  FOR value IN 
      ('+SUBSTRING(@Fields, 1, LEN(@Fields)-1)+')
)AS unpvt) AS SourceTable
PIVOT
(
MAX(FieldName)
FOR KeyID IN ('+SUBSTRING(@keyIDs1, 1, LEN(@keyIDs1)-1)+')
) AS PivotTable
WHERE '+@whereClauseParam

PRINT(@SQL2);
EXECUTE(@SQL2);

Result:

结果：

In response to the comment "This returns error: Invalid length parameter passed to the LEFT or SUBSTRING function :)":

响应注释“这返回错误：传递给LEFT或SUBSTRING函数的无效长度参数:)”：