pivot with a twist - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

pivot with a twist

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-08-22 : 18:34:56

Greetings,

I keep track of localization/translation info for our website. Currently the translation is happening as follows.

One table with the word in English in one column and the translated terms are on the same table with columns for each of the different languages. How do I go about to dynamically get language codes as column headers and translated items under it's own respective languagecode column?


CREATE TABLE [dbo].[Untranslated](
	[ID] [int] NOT NULL,
	[EnglishTerm] [varchar](100) NOT NULL,
	[TranslatedTerm] [varchar](100) NULL,
	[IsTranslated] [bit] NOT NULL,
	[TranslatedLanguageCode] [varchar](10) NULL
) ON [PRIMARY]


insert into [Untranslated]
select 1, 'Tree', 'Arbre',1,'FR'
UNION
select 2, 'Tree', 'Arbol',1,'ES'
UNION
select 2, 'Tree', 'Zaf',1,'AM'

Desired Result
EnglishTerm   FR     ES    AM
       Tree  Arbre  Arbol Zaf

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-22 : 19:59:11

I'm surprised you aren't using nvarchar data type. I guess you don't support languages that require unicode such as Chinese?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-22 : 20:26:40

And for your question on pivoting, search for dynamic pivot. There are lots of example

KH
[spoiler]Time is always against us[/spoiler]

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-08-22 : 21:47:47

Thank you both.

TK: this is just sample table but you are absolutely right I will be using nvarchar
KH: I had that sproc from Madhivan but was unaware I could use MAX(txtField)

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-23 : 00:21:38

yes. you can. MAX() does not necessary for numeric only, it also works on string

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources