SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Inner Join? Group by?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pablowerk
Starting Member

3 Posts
Posted - 05/09/2007 :  10:08:03  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts
Posted - 05/09/2007 :  10:45:09  Show Profile  Visit nr's Homepage  Reply with Quote
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 - 05/09/2007 :  12:59:39  Show Profile  Reply with Quote
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

India
35 Posts
Posted - 05/10/2007 :  03:11:07  Show Profile  Click to see zubairmasoodi's MSN Messenger address  Send zubairmasoodi a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5509 Posts
Posted - 05/10/2007 :  03:17:37  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Check these articles:

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

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000