ON C.Description = D.TextID AND D.LanguaugeID = 1 ![]() WHERE A.Title = B.TextID AND B.LanguaugeID = 1) C (SELECT A.ID, A.Description, B.Text AS Title Currently when I have two text items in the table this is what I do: SELECT C.ID, C.Title, D.Text AS Description Ideally I want a query that I can use to select from the table, and get the text as opposed to the ids of the text out of the table. My question is more a call for suggestions on how to best handle this. This allows me to by adding a languageID column to Table B, have multiple translations for the same row in the database. Anytime text is used within the table, instead of storing actual varchar values, ids are stored that relate back to text stored in Table B. So, for example, Table A contains the actual content. The view would have one row for each language so you could select a row-specific language by constraining LanguageID.īasically, I have two tables, Table A contains the actual items that I care to get out, and Table B is used for language translations. It would have columns for ID, LanguageID, TitleText, DescriptionText, etc. So TableA is basically a table with all foreign keys to TableB? You could create a view that included all of the joins to TableB. You can, however, overcome this by writing dynamic SQL. The only disadvantage to a PIVOT in TSQL is that you have to identify the number of pivot columns beforehand (when you write the query). ID, Title, Description, LanguageId, Text1, Text2, Text3, Text4 You could write a query to PIVOT out the text so that you can get the following output In that case look up the PIVOT functionality here It sounds like you want would benifit from converting some of the row data to column data. 3 columns for 100 rows = 300 function calls), but if you’re only returning one row at a time, it might not perform so poorly. This might not perform particularly well, as you have to call the function once for each translated column for each row returned (i.e. WHERE TextId = LanguageId = write the query as: SELECT SELECT - Reserved wordin SQL, rename that column! ![]() RETURNS nvarchar(100) - Should use nvarchar, and set to max length of string Look into using a function to return data for each column that requires translation.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |