T-SQL: for Loop, Temporary (in-memory) loops and stored procedures

Question

I have a database structure, for example:

SELECT * FROM Culture; ------------------------ Id ShortName FullName Supported 22 en-US English (United States) 1 23 fr-FR French (France) 1 24 hi-IN Hindi (India) 0 SELECT * FROM ResourceKey; ---------------------------- Id Name 20572 HowAreYou 20571 Hello SELECT * FROM Strings; ----------------------- Id CultureId ResourceKeyId ResourceValue 41133 22 20571 Hello 41134 22 20572 How are you? 41135 23 20571 Bonjour 41136 23 20572 Comment allez-vous? SELECT * FROM Category; ------------------------ Id Name 1 JavaScript SELECT * FROM StringCategory; ------------------------------ Id ResourceKeyId CategoryId 1 20571 1 2 20572 1 

I want the SQL query to give me the following results:

 Key CategoryName en-US fr-FR another-SupportedCulture And so on ------------------------------------------------------------------------------------------------------------------------------------------------------------- Hello JavaScript Hello Bonjour Value of hello in that supported culture and so on for all supported cultures HowAreYou JavaScript How are you? Comment allez-vous? Value of HowAreYou in that supported culture and so on for all supported cultures 

I know how to write a query for a particular culture. I believe that I will write individual queries for all cultures in the Culture table, whose Supported field is set to 1 ( true ). Then I applied the LEFT OUTER JOIN between the English culture (which will have all the meanings) and all other cultures to get the above dataset.

So far, I have written so far for a separate culture:

 SELECT ResourceKey.Id AS ResourceKeyId, ResourceKey.Name AS [Key], Category.Name AS CategoryName, Culture.Id AS CultureId, Culture.ShortName AS CultureShortName, Strings.ResourceValue FROM ResourceKey LEFT OUTER JOIN Strings ON ResourceKey.Id = Strings.ResourceKeyId INNER JOIN Culture ON Strings.CultureId = Culture.Id LEFT OUTER JOIN StringCategory ON ResourceKey.Id = StringCategory.ResourceKeyId LEFT OUTER JOIN Category ON StringCategory.CategoryId = Category.Id WHERE Strings.CultureId = 23; -- this will be a T-SQL stored procedure parameter I understand 

Which returns the following results:

 ResourceKeyId Key CategoryName CultureId CultureShortName ResourceValue ---------------------------------------------------------------------------------- 20571 Hello JavaScript 23 fr-FR Bonjour 20572 HowAreYou JavaScript 23 fr-FR Comment allez-vous? 

Thing is: I'm not sure how to start writing a loop in T-SQL and then store these separate datasets in a temporary memory area (I believe there is a term for it, for some table type expressions that are in memory and only for of the entire T-SQL period, specifically for situations like mine) in the SQL process, and then applying the join to them. This is where I need help.

Thank you for your patience in reading my question and help.

I am using Microsoft SQL Server 2008 Express R2.

Answer 1

If you want a dynamic number of columns, you need to use dynamic SQL:

 declare @stmt nvarchar(max) select @stmt = isnull(@stmt + ', ', '') + 'max(case when s.CultureId = ' + cast(c.Id as nvarchar(max)) + ' then s.ResourceValue end) as ' + quotename(c.ShortName) from Culture as c where c.Supported = 1 select @stmt = ' select rk.Name as [Key], c.Name as CategoryName, ' + @stmt + ' from StringCategory as sc inner join Category as c on c.Id = sc.CategoryId inner join ResourceKey as rk on rk.Id = sc.ResourceKeyId inner join Strings as s on s.ResourceKeyId = rk.Id group by rk.Name, c.Name ' exec sp_executesql @stmt = @stmt; 

demo sql

Answer 2

I learned from a colleague that I need to apply PIVOT. T-SQL and SQL Server 2005 and higher support the PIVOT operation. Here are some links to why PIVOT is useful in a situation like the one described above, and how to do it:

http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query