I am having trouble trying to figure out this querry. My end goal is to create a matrix displaying which documents are available in which languages.Here is my table and some data:CREATE TABLE [mlp_DocumentType_Product_map] ( [DocumentTypeID] [int] NULL , [ProductID] [int] NULL , [LanguageID] [int] NULL , [DocumentLocation] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]GOINSERT INTO mlp_DocumentType_Product_map (DocumentTypeID, ProductID, LanguageID, DocumentLocation) VALUES (1, 8, 1, 'pdf/file_English.pdf')INSERT INTO mlp_DocumentType_Product_map (DocumentTypeID, ProductID, LanguageID, DocumentLocation) VALUES (1, 8, 2, 'pdf/file_German.pdf')INSERT INTO mlp_DocumentType_Product_map (DocumentTypeID, ProductID, LanguageID, DocumentLocation) VALUES (1, 8, 3, 'pdf/file_Russian.pdf')INSERT INTO mlp_DocumentType_Product_map (DocumentTypeID, ProductID, LanguageID, DocumentLocation) VALUES (1, 8, 7, 'pdf/file_Spanish.pdf')INSERT INTO mlp_DocumentType_Product_map (DocumentTypeID, ProductID, LanguageID, DocumentLocation) VALUES (1, 9, 1, 'pdf/file_English.pdf')INSERT INTO mlp_DocumentType_Product_map (DocumentTypeID, ProductID, LanguageID, DocumentLocation) VALUES (1, 9, 3, 'pdf/file_Russian.pdf')INSERT INTO mlp_DocumentType_Product_map (DocumentTypeID, ProductID, LanguageID, DocumentLocation) VALUES (1, 7, 2, 'pdf/file_German.pdf')INSERT INTO mlp_DocumentType_Product_map (DocumentTypeID, ProductID, LanguageID, DocumentLocation) VALUES (1, 7, 3, 'pdf/file_Russian.pdf')INSERT INTO mlp_DocumentType_Product_map (DocumentTypeID, ProductID, LanguageID, DocumentLocation) VALUES (1, 7, 7, 'pdf/file_Spanish.pdf')
Using the following query I am able to grab the Distinct DocumentLocations:SELECT DISTINCT E1.DocumentLocationFROM mlp_DocumentType_Product_map E1 INNER JOIN mlp_DocumentType_Product_map E2 ON E1.ProductID = E2.ProductIDWHERE (E1.ProductID = 8) AND (E1.DocumentTypeID = 1)
Output:DocumentLocationpdf/file_English.pdfpdf/file_German.pdfpdf/file_Russian.pdfpdf/file_Spanish.pdfI would like to use Alias's to get the output to be formatted this way:DocumentTypeID ProductID EnglishFile GermanFile RussianFile SpanishFile1 7 Null pdf/file_German.pdf pdf/file_Russian.pdf pdf/file_Spanish.pdf1 8 pdf/file_English.pdf pdf/file_German.pdf pdf/file_Russian.pdf pdf/file_Spanish.pdf1 9 pdf/file_English.pdf Null pdf/file_Russian.pdf Null
Any help or direction would be greatly appreciated.Thanks