November 24, 2010 at 4:48 am
Hello,
I have a table like following:
[font="Courier New"]
Item No. Language Description
1 EN descr. english1
1 IT descr. italian1
2 EN descr. english2
2 IT descr. italian2
3 EN descr. english3
3 IT descr. italian3
[/font]
I need a select statement to produce the table like following:
1 descr.english1 descr.italian1
2 descr.english1 descr.italian1
3 descr.english1 descr.italian1
Which statement should I use?
Thank you!
November 24, 2010 at 5:16 am
Not sure if i understood your requirement correctly, but i hope this is what you want.
DECLARE@tbl_Items TABLE
(
Item_NoINT,
LanguageVARCHAR(10),
DescriptionVARCHAR(100)
)
INSERT@tbl_Items
SELECT1, 'EN', 'descr. english1' UNION ALL
SELECT1, 'IT', 'descr. italian1' UNION ALL
SELECT2, 'EN', 'descr. english2' UNION ALL
SELECT2, 'IT', 'descr. italian2' UNION ALL
SELECT3, 'EN', 'descr. english3' UNION ALL
SELECT3, 'IT', 'descr. italian3'
SELECTItem_No,
MAX( CASE WHEN Language = 'EN' THEN Description ELSE '' END ) EN,
MAX( CASE WHEN Language = 'IT' THEN Description ELSE '' END ) IT
FROM @tbl_Items I
GROUP BY Item_No
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 24, 2010 at 6:32 am
select [Item No.], REPLACE([Description],[Description],'descr.english1') [Description],
(select [Description] from tempdb.dbo.Test1 where [Item No.] = 1 and [Language] = 'IT') [Description]
from tempdb.dbo.Test1 where [Language] = 'EN'
November 24, 2010 at 6:33 am
I actually have a table with translations in several languages. As you can see in my example, I have placed two different translations, one english and another italian. There can be max 4 translations, but most items are translated in only two languages so far.
In a database the table is structured like this:
ITEMNO1 EN "DESCRIPTION IN ENGLISH"
ITEMNO1 IT "DESCRIPTION IN ITALIAN"
ITEMNO2 EN "DESCRIPTION IN ENGLISH"
ITEMNO2 IT "DESCRIPTION IN ITALIAN"
ITEMNO3 EN "DESCRIPTION IN ENGLISH"
ITEMNO3 IT "DESCRIPTION IN ITALIAN"
ITEMNO3 DE "DESCRIPTION IN GERMAN"
ITEMNO3 RU "DESCRIPTION IN RUSSIAN"
As you can see from this example, the item number in ITEMNO column can repeat as many times as there're translations given for this item.
What I need is to make a query that will produce a table like this:
ITEMNO1 "DESCRIPTION IN ENGLISH" "DESCRIPTION IN ITALIAN"
ITEMNO2 "DESCRIPTION IN ENGLISH" "DESCRIPTION IN ITALIAN"
ITEMNO3 "DESCRIPTION IN ENGLISH" "DESCRIPTION IN ITALIAN" "DESCRIPTION IN GERMAN" "DESCRIPTION IN RUSSIAN"
November 24, 2010 at 6:56 am
zbakhramov (11/24/2010)
I actually have a table with translations in several languages. As you can see in my example, I have placed two different translations, one english and another italian. There can be max 4 translations, but most items are translated in only two languages so far.In a database the table is structured like this:
ITEMNO1 EN "DESCRIPTION IN ENGLISH"
ITEMNO1 IT "DESCRIPTION IN ITALIAN"
ITEMNO2 EN "DESCRIPTION IN ENGLISH"
ITEMNO2 IT "DESCRIPTION IN ITALIAN"
ITEMNO3 EN "DESCRIPTION IN ENGLISH"
ITEMNO3 IT "DESCRIPTION IN ITALIAN"
ITEMNO3 DE "DESCRIPTION IN GERMAN"
ITEMNO3 RU "DESCRIPTION IN RUSSIAN"
As you can see from this example, the item number in ITEMNO column can repeat as many times as there're translations given for this item.
What I need is to make a query that will produce a table like this:
ITEMNO1 "DESCRIPTION IN ENGLISH" "DESCRIPTION IN ITALIAN"
ITEMNO2 "DESCRIPTION IN ENGLISH" "DESCRIPTION IN ITALIAN"
ITEMNO3 "DESCRIPTION IN ENGLISH" "DESCRIPTION IN ITALIAN" "DESCRIPTION IN GERMAN" "DESCRIPTION IN RUSSIAN"
here's how I've done it:
select Item
, max(case when language = 'TR' then Description end) Turkish
, max(case when language = 'EN' then Description end) English
, max(case when language = 'IT' then Description end) Italiano
, max(case when language = 'DE' then Description end) German
, max(case when language = 'RU' then Description end) Russian
, max(case when language = 'EL' then Description end) Greek
, max(case when language = 'DK' then Description end) Dutch
, max(case when language = 'FR' then Description end) French
, max(case when language = 'NL' then Description end) Netherlands
, max(case when language = 'PT' then Description end) Portugese
, max(case when language = 'ES' then Description end) Spanich
--- etc for other languages
from dbo.MA_ItemsLanguageDescri
group by Item
November 24, 2010 at 6:59 am
select [Item No.], [Description],
(select [Description] from tempdb.dbo.Test1 where [Item No.] = 'ITEMNO1' and [Language] = 'IT'),
(select [Description] from tempdb.dbo.Test1 where [Item No.] = 'ITEMNO1' and [Language] = 'DE'),
(select [Description] from tempdb.dbo.Test1 where [Item No.] = 'ITEMNO1' and [Language] = 'RU')
from tempdb.dbo.Test1 where [Item No.] = 'ITEMNO1' and [Language] = 'EN'
UNION
select [Item No.], [Description],
(select [Description] from tempdb.dbo.Test1 where [Item No.] = 'ITEMNO2' and [Language] = 'IT'),
(select [Description] from tempdb.dbo.Test1 where [Item No.] = 'ITEMNO2' and [Language] = 'DE'),
(select [Description] from tempdb.dbo.Test1 where [Item No.] = 'ITEMNO2' and [Language] = 'RU')
from tempdb.dbo.Test1 where [Item No.] = 'ITEMNO2' and [Language] = 'EN'
UNION
select [Item No.], [Description],
(select [Description] from tempdb.dbo.Test1 where [Item No.] = 'ITEMNO3' and [Language] = 'IT'),
(select [Description] from tempdb.dbo.Test1 where [Item No.] = 'ITEMNO3' and [Language] = 'DE'),
(select [Description] from tempdb.dbo.Test1 where [Item No.] = 'ITEMNO3' and [Language] = 'RU')
from tempdb.dbo.Test1 where [Item No.] = 'ITEMNO3' and [Language] = 'EN'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply