StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>AFAIK, you can do this on the server-side only with a dynamic <code>SQL</code> stored procedure. </p> <p>Effectively the code you want to generate dynamically is:</p> <pre><code>SELECT [Data Table].* ,[MyTable Date].MetaData ,[MyTable Name].MetaData FROM [Data Table] LEFT JOIN [MyTable] AS [MyTable Date] ON [MyTable Date].DataID = [Data Table].DataID AND [MyTable Date].MetaName = 'Date' LEFT JOIN [MyTable] AS [MyTable Name] ON [MyTable Name].DataID = [Data Table].DataID AND [MyTable Name].MetaName = 'Name' </code></pre> <p>And here's code to do it:</p> <pre><code>DECLARE @sql AS varchar(max) DECLARE @select_list AS varchar(max) DECLARE @join_list AS varchar(max) DECLARE @CRLF AS varchar(2) DECLARE @Tab AS varchar(1) SET @CRLF = CHAR(13) + CHAR(10) SET @Tab = CHAR(9) SELECT @select_list = COALESCE(@select_list, '') + @Tab + ',[MyTable_' + PIVOT_CODE + '].[MetaData]' + @CRLF ,@join_list = COALESCE(@join_list, '') + 'LEFT JOIN [MyTable] AS [MyTable_' + PIVOT_CODE + ']' + @CRLF + @Tab + 'ON [MyTable_' + PIVOT_CODE + '].DataID = [DataTable].DataID' + @CRLF + @Tab + 'AND [MyTable_' + PIVOT_CODE + '].MetaName = ''' + PIVOT_CODE + '''' + @CRLF FROM ( SELECT DISTINCT MetaName AS PIVOT_CODE FROM [MyTable] ) AS PIVOT_CODES SET @sql = 'SELECT [DataTable].*' + @CRLF + @select_list + 'FROM [DataTable]' + @CRLF + @join_list PRINT @sql --EXEC (@sql) </code></pre> <p>You could use a similar dynamic technique using the <code>CASE</code> statement example to perform the pivot.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload