Question

在SQL Server 2005 中,是否可以在不逐行处理的情况下在 SQL Server 2005 中做以下操作? 如果是的话,如何操作? :

我的桌子是这样的:

╔════════════╦═══════════╦════════════╗
║ CustomerID ║ FirstName ║  LastName  ║
╠════════════╬═══════════╬════════════╣
║          1 ║ George    ║ Washington ║
║          2 ║ Benjamin  ║ Franklin   ║
║          3 ║ Thomas    ║ Jefferson  ║
╚════════════╩═══════════╩════════════╝

I 输出上表像这样的 :

╔════════════╦══════════╦════════════╗
║   Field    ║ IntValue ║ TextValue  ║
╠════════════╬══════════╬════════════╣
║ CustomerID ║ 1        ║ NULL       ║
║ FirstName  ║ NULL     ║ George     ║
║ LastName   ║ NULL     ║ Washington ║
║ CustomerID ║ 2        ║ NULL       ║
║ FirstName  ║ NULL     ║ Benjamin   ║
║ LastName   ║ NULL     ║ Franklin   ║
║ CustomerID ║ 3        ║ NULL       ║
║ FirstName  ║ NULL     ║ Thomas     ║
║ LastName   ║ NULL     ║ Jefferson  ║
╚════════════╩══════════╩════════════╝

Thanks! Jason

Answer 1

这只扫描过一次表格( 从 @ Aaron s 答案浏览表格变量 ) 。

SELECT Field, 
       IntValue, 
       TextValue 
FROM   @x 
       CROSS APPLY (SELECT  CustomerID , CustomerID,  NULL 
                    UNION ALL 
                    SELECT  FirstName , NULL, FirstName
                    UNION ALL 
                    SELECT  LastName , NULL, LastName) 
        CA(Field, IntValue, TextValue) 
ORDER BY CustomerID

Answer 2

虽然我仍然认为这个支线在SELECT的消耗层里效果更好, 但有一个想法。

DECLARE @x TABLE(CustomerID INT, FirstName VARCHAR(32), LastName NVARCHAR(32));

INSERT @x SELECT 1,  George ,  Washington 
UNION ALL SELECT 2,  Benjamin ,  Franklin 
UNION ALL SELECT 3,  Thomas ,  Jefferson ;

;WITH x AS
(
  SELECT 
    Field =  CustomerID , IntValue = CustomerID, TextValue = NULL,
    rn = ROW_NUMBER() OVER (ORDER BY CustomerID)
    FROM @x
  UNION ALL
  SELECT 
    Field =  FirstName , NULL, FirstName,
    rn = ROW_NUMBER() OVER (ORDER BY CustomerID)
    FROM @x
  UNION ALL
  SELECT
    Field =  LastName , NULL, LastName,
    rn = ROW_NUMBER() OVER (ORDER BY CustomerID)
    FROM @x
)
SELECT Field, IntValue, TextValue
FROM x
ORDER BY rn, Field;

为什么稍后的演示会做得更好?因为这个“解答”会扫描三张桌子。消费者仍然需要用一个循环来显示结果...

友情链接