Dynamically display rows as columns

Question

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 have more or less completely restructured and gives the user a dump of all of the product information at once. So I need to do some inner joins to get all of these tables together. I know how to do basic inner joins but I am stuck on one detail. There are a few tables where there are multiple entries for each item. For example, the CrossReference table may have multiple cross reference numbers for an item, or it may only have one, or it may have none at all. Is it possible to have those placed dynamically into separate columns. so this:

Item         CrossReferenceNumber 
XXXXX        crossref1 
XXXXX        crossref2 
XXXXX        crossref3

could become this (after a join with some other tables):

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

But if there were no cross references, there would be no cross reference columns. Is something like that possible or am I dreaming?

Answer 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 (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 */