Dynamically Display Rows As Columns

I couldn't think of a good way to word the title, if anyone can come up with something better please feel free. Basically there is an old VB6 app that pulls data from a db that I h

Solution 1:

Oracle 11g and Sql Server 2005+ both contain a pivot command that will accomplish what you want.

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

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

Otherwise you would need to build a dynamic sql statement to achieve this.

Edit - Here you go (SQL Server version).

/* Begin Set up of test data */
IF EXISTS (SELECT1from sys.tables WHERE name = N'Item')
    DROPTABLE Item
GO

IF EXISTS (SELECT1from sys.tables WHERE name = N'CrossReference')
    DROPTABLE CrossReference
GO


CREATETABLE Item
(
    Item        varchar(20),
    BasePart    varchar(20),
    Size        varchar(20)
);

CREATETable CrossReference 
(
    Item        varchar(20),
    CrossReferenceNumber    varchar(20)
);

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

INSERTINTO CrossReference VALUES( 'item1', 'crossRef1')
INSERTINTO CrossReference VALUES('item1', 'crossRef2')
INSERTINTO CrossReference VALUES('item1', 'crossRef3')
INSERTINTO CrossReference VALUES('item1', 'crossRef4')
INSERTINTO CrossReference VALUES('item2', 'crossRef1')
INSERTINTO CrossReference VALUES('item2', 'crossRef1')
INSERTINTO CrossReference VALUES('item3', 'crossRef1')
INSERTINTO CrossReference VALUES('item4', 'crossRef2')
INSERTINTO CrossReference VALUES('item5', 'crossRef5')
INSERTINTO CrossReference VALUES('item5', 'crossRef1')
INSERTINTO CrossReference VALUES('item5', 'crossRef2')
INSERTINTO CrossReference VALUES('item5', 'crossRef3')
/* End of test data setup *//* Begin of actual query */DECLARE@xRefsVARCHAR(2000),
        @queryVARCHAR(8000)

SELECT@xRefs= STUFF((SELECTDISTINCT'],['+ ltrim(CrossReferenceNumber)
                        FROM CrossReference
                        ORDERBY'],['+ 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 */