November 11, 2008 at 10:21 am
Hi Seth Phelabaum,
DECLARE @Language VARCHAR(10)
SET @Language = 'JP'
SELECT DISTINCT T1.ID, COALESCE(T2.Name, T3.Name) Name, COALESCE(T2.Description, T3.Description) Description,
COALESCE(T2.Language, T3.Language) Language
FROM #Table1 T1
LEFT JOIN #Table1 T2 ON T1.ID = T2.ID AND T2.Language = @Language
LEFT JOIN #Table1 T3 ON T1.ID = T3.ID AND T3.Language = 'EN'
ORDER BY ID
The above works for two languages
i.e SELECT DISTINCT T1.ID, COALESCE(T2.Name, T3.Name) Name, COALESCE(T2.Description, T3.Description) Description,
COALESCE(T2.Language, T3.Language) Language
FROM #Table1 T1
LEFT JOIN #Table1 T2 ON T1.ID = T2.ID AND T2.Language = 'ja'
LEFT JOIN #Table1 T3 ON T1.ID = T3.ID AND T3.Language = 'EN'
ORDER BY ID
For my requirement I tried for 3 Joins
SELECT DISTINCT T1.ID, COALESCE(COALESCE(T2.Name, T3.Name),COALESCE(T3.Name, T4.Name)) Name FROM #Table1 T1
LEFT JOIN #Table1 T2 ON T1.ID = T2.ID AND T2.Language = 'es-ar'
LEFT JOIN #Table1 T3 ON T1.ID = T3.ID AND T3.Language = 'es'
LEFT JOIN #Table1 T4 ON T1.ID = T4.ID AND T4.Language = 'EN
ORDER BY ID
Let me explain my requirement 1st Check for language 'es-ar'
else check for 'es' ese 'EN'
It takes time to execute the QUERY
Here is my actual data..
Select(ElementID,'ElementName',VersionID,'language',ID)
Select(72,'Apparel/Shoes',173,'en',10)
Select(73,'Asphalt/Rock/Concrete',174,'en',11)
Select(74,'Biomedical',175,'en',12)
Select(75,'Ceramics/Glass',176,'en',13)
Select(76,'Components',177,'en',16)
Select(77,'Composites',178,'en',17)
Select(78,'Electronics/Electrical',179,'en',19)
Select(80,'Films',181,'en',21)
Select(81,'Foam',182,'en',22)
Select(82,'Food',183,'en',23)
Select(83,'Packaging',184,'en',24)
Select(84,'Paper',185,'en',26)
Select(85,'Springs',186,'en',27)
Select(86,'Textiles',187,'en',28)
Select(88,'Wood',189,'en',30)
Select(136,'Rubbers/Elastomers',240,'en',112)
Select(7,'Plastics',759,'en',298)
Select(536,'Biomaterials',1835,'en',490)
Select(764,'Soil',2298,'en',652)
Select(5,'Metals',2405,'en',588)
Select(71,'Adhesives',2570,'en',636)
Select(1044,'Fasteners',3758,'en',867)
Select(1296,'??? ????',4818,'en',1216)
Select(1298,'aaaaaaaaaaaaaa',4824,'en',1126)
Select(71,'?????????? ',4829,'en',1266)
Select(72,'??????????/??????????',4831,'en',1267)
Select(88,'????????? ',4833,'en',1268)
Select(5,'??????',4835,'en',1269)
Select(7,'?????????? plastics ?????????? ',4850,'en',1131)
Select(71,'Adhesivos',4868,'en',1271)
Select(1305,'Zapatos',4873,'en',1135)
Select(71,'Adhesivosar',4891,'en',1221)
Select(72,'Zappatos',4895,'en',1138)
Can you please help.
November 11, 2008 at 10:37 am
Guess this wasn't that urgent after all eh? :hehe:
In any case, you were close. (Next time, explain how you tried to modify it and say that your modifications didn't work)
The coalesce function returns the first NON NULL value in the list, left to right. With that in mind, your code should have looked like this for the 3 language requirement:
[font="Courier New"]SELECT DISTINCT T1.ID,
COALESCE(T2.Name, T3.Name, T4.Name) [Name],
COALESCE(T2.Description, T3.Description, T4.Description) [Description],
COALESCE(T2.Language, T3.Language, T4.Language) [Language]
FROM #Table1 T1
LEFT JOIN #Table1 T2 ON T1.ID = T2.ID AND T2.Language = 'es-ar'
LEFT JOIN #Table1 T3 ON T1.ID = T3.ID AND T3.Language = 'es'
LEFT JOIN #Table1 T4 ON T1.ID = T4.ID AND T4.Language = 'EN'
ORDER BY T1.ID[/font]
To make it variable again, just replace the 'es-ar' with @Language.
P.S. All the values in your "actual data" contain only 'en' for language.
November 11, 2008 at 11:31 am
Thanks...
But the only problem is Description is ntext field and Distinct doen't work on this type.
I cannot convert to nvarhar(4000) since only 4000 charactes will be fetched.
:hehe:
its taking time to fetch 31 rows of this condition :hehe:
And # table1 is a view in my Case.
Could you help me please
November 11, 2008 at 12:21 pm
What happened to varchar 100! 😀
How about something like this:
[font="Courier New"]SELECT T1.ID,
COALESCE(T2.Name, T3.Name, T4.Name) [Name],
COALESCE(T2.Language, T3.Language, T4.Language) [Language],
(SELECT [Description] FROM #Table1 WHERE ID = T1.ID AND Language = COALESCE(T2.Language, T3.Language, T4.Language)) [Description]
FROM #Table1 T1
LEFT JOIN #Table1 T2 ON T1.ID = T2.ID AND T2.Language = 'es-ar'
LEFT JOIN #Table1 T3 ON T1.ID = T3.ID AND T3.Language = 'es'
LEFT JOIN #Table1 T4 ON T1.ID = T4.ID AND T4.Language = 'EN'
ORDER BY T1.ID
[/font]
[EDIT] I wrote that initial response 10 seconds before I walked out the door to an OPASS meeting so it was a bit hurried. I just removed the DISTINCT. You shouldn't need the DISTINCT in there in the first place. I have a bad habit of adding it to queries that I really shouldn't sometimes.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply