动态地将行显示为列

Question

我想不出更好的方式来表达标题，如果有人能想出更好的东西，请随意。基本上有一个旧的 VB6 应用程序，它从我或多或少完全重组的数据库中提取数据，并立即向用户提供所有产品信息的转储。所以我需要做一些内部联接来将所有这些表放在一起。我知道如何进行基本的内部联接，但我坚持一个细节。有一些表，其中每个项目都有多个条目。例如，CrossReference 表中的一项可能有多个交叉引用编号，也可能只有一个，或者根本没有。是否可以将它们动态放置到单独的列中。所以 this:

Item         CrossReferenceNumber 
XXXXX        crossref1 
XXXXX        crossref2 
XXXXX        crossref3

可能会变成这样（与其他一些表连接后）：

Item  BasePart Size   CrossReferenceNumber1  CrossReferenceNumber2  CrossReferenceNumber3
XXXX  XXXX     Large  crossref1              crossref2              crossref3

但如果没有交叉引用，就不会有交叉引用列。这样的事情可能发生还是我在做梦？

Answer 1

Oracle 11g 和 Sql Server 2005+ 都包含一个可以完成您想要的任务的数据透视命令。

http://www.orafaq.com/wiki/PIVOT

http://msdn.microsoft.com/en-us/library/ms177410.aspx

否则您需要构建一个动态 sql 语句来实现此目的。

编辑 - 给你（SQL Server 版本）。

/* Begin Set up of test data */
IF EXISTS (SELECT 1 from sys.tables WHERE name = N'Item')
    DROP TABLE Item
GO

IF EXISTS (SELECT 1 from sys.tables WHERE name = N'CrossReference')
    DROP TABLE CrossReference
GO


CREATE TABLE Item
(
    Item        varchar(20),
    BasePart    varchar(20),
    Size        varchar(20)
);

CREATE Table CrossReference 
(
    Item        varchar(20),
    CrossReferenceNumber    varchar(20)
);

INSERT INTO Item VALUES ('item1', 'b1', 'Large');
INSERT INTO Item VALUES ('item2', 'bxx1', 'Large');
INSERT INTO Item VALUES ('item3', 'bddf1', 'Small');
INSERT INTO Item VALUES ('item4', 'be3f1', 'Small');
INSERT INTO Item VALUES ('item5', 'b13vx1', 'Small');

INSERT INTO CrossReference VALUES( 'item1', 'crossRef1')
INSERT INTO CrossReference VALUES('item1', 'crossRef2')
INSERT INTO CrossReference VALUES('item1', 'crossRef3')
INSERT INTO CrossReference VALUES('item1', 'crossRef4')
INSERT INTO CrossReference VALUES('item2', 'crossRef1')
INSERT INTO CrossReference VALUES('item2', 'crossRef1')
INSERT INTO CrossReference VALUES('item3', 'crossRef1')
INSERT INTO CrossReference VALUES('item4', 'crossRef2')
INSERT INTO CrossReference VALUES('item5', 'crossRef5')
INSERT INTO CrossReference VALUES('item5', 'crossRef1')
INSERT INTO CrossReference VALUES('item5', 'crossRef2')
INSERT INTO CrossReference VALUES('item5', 'crossRef3')
/* End of test data setup */

/* Begin of actual query */
DECLARE @xRefs VARCHAR(2000),
        @query VARCHAR(8000)

SELECT @xRefs = STUFF((SELECT DISTINCT '],[' + ltrim(CrossReferenceNumber)
                        FROM CrossReference
                        ORDER BY '],[' + ltrim(CrossReferenceNumber)
                        FOR XML PATH('')
                       ), 1, 2, '') + ']'

SET @query = 
    'SELECT * 
     FROM   Item i
            INNER JOIN 
            (
                SELECT * 
                FROM
                (
                    SELECT Item, CrossReferenceNumber
                    FROM CrossReference
                ) t
                PIVOT   (MAX(CrossReferenceNumber) FOR CrossReferenceNumber IN (' + @xRefs + ')) as pvt

            ) xRefs
                ON i.Item = xRefs.Item
     ORDER BY i.Item'

EXECUTE (@query)
/* end */