Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Inner Join? Group by?

Author  Topic 

pablowerk
Starting Member

3 Posts

Posted - 2007-05-09 : 10:08:03
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]
GO


INSERT 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.DocumentLocation
FROM mlp_DocumentType_Product_map E1 INNER JOIN
mlp_DocumentType_Product_map E2 ON E1.ProductID = E2.ProductID
WHERE (E1.ProductID = 8) AND (E1.DocumentTypeID = 1)


Output:
DocumentLocation
pdf/file_English.pdf
pdf/file_German.pdf
pdf/file_Russian.pdf
pdf/file_Spanish.pdf

I would like to use Alias's to get the output to be formatted this way:
DocumentTypeID	ProductID	EnglishFile		GermanFile		RussianFile		SpanishFile
1 7 Null pdf/file_German.pdf pdf/file_Russian.pdf pdf/file_Spanish.pdf
1 8 pdf/file_English.pdf pdf/file_German.pdf pdf/file_Russian.pdf pdf/file_Spanish.pdf
1 9 pdf/file_English.pdf Null pdf/file_Russian.pdf Null



Any help or direction would be greatly appreciated.

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-09 : 10:45:09
select DocumentTypeID, ProductID ,
EnglishFile = max(case when LanguageID = 1 then DocumentLocation else '') end ,
GermanFile = max(case when LanguageID = 2 then DocumentLocation else '') end ,
RussianFile = max(case when LanguageID = 3 then DocumentLocation else '') end ,
SpanishFile = max(case when LanguageID = 7 then DocumentLocation else '') end ,
from mlp_DocumentType_Product_map
group by DocumentTypeID, ProductID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

pablowerk
Starting Member

3 Posts

Posted - 2007-05-09 : 12:59:39
Thanks nr,

I had to do a little tweaking to your code, but I got it to work!

Thanks a bunch!

Here's my updated code:
SELECT     DocumentTypeID, ProductID, EnglishFile = MAX(CASE LanguageID  WHEN 1 THEN DocumentLocation ELSE 'no' END), 
GermanFile = MAX(CASE WHEN LanguageID = 2 THEN DocumentLocation ELSE 'no' END),
RussianFile = MAX(CASE WHEN LanguageID = 3 THEN DocumentLocation ELSE 'no' END),
SpanishFile = MAX(CASE WHEN LanguageID = 7 THEN DocumentLocation ELSE 'no' END)
FROM mlp_DocumentType_Product_map
GROUP BY DocumentTypeID, ProductID
Go to Top of Page

zubairmasoodi
Starting Member

35 Posts

Posted - 2007-05-10 : 03:11:07
What if we 1000 different langauges. do we need to
Write one Such statement for Each Language
There Must be some other wayOut

Zubair
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-10 : 03:17:37
Check these articles:

[url]http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx[/url]
[url]http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page
   

- Advertisement -