Dynamic rotation help in SQL2005

Question

I have a table of name value pairs where the tags are stored:

TAGID | NAME | VALUE 

I have a table of "Things" that apply to tags

 THINGID | TAGID 

I need a query to create a result set if the columns / fields are all possible TAG-NAMES (field NAME in the TAG table) for this THINGID, and the values ​​are the corresponding tag values.

 THINGID | TAGNAME1 | TAGNAME2 | ... |etc. 

I can find examples with fixed columns, but nothing like that.

Answer 1

Something similar to a slightly different scheme (thing_id, tag_name, tag_value) - without a tag table:

 CREATE PROCEDURE dbo.PivotData(@table VARCHAR(128), @basefield VARCHAR(128), @namefield VARCHAR(128), @valuefield VARCHAR(128)) AS DECLARE @sql NVARCHAR(MAX) SET @sql = 'DECLARE @colName VARCHAR(128), @sqlBegin NVARCHAR(MAX), @sqlMiddle NVARCHAR(MAX), @sqlEnd NVARCHAR(MAX), @counter INT SET @counter = 1 SET @sqlBegin = N''SELECT DISTINCT t0.'' + ''' + QUOTENAME(@basefield) + ''' SET @sqlMiddle = N'' FROM '' + ''' + QUOTENAME(@table) + ''' + '' AS t0 '' DECLARE cols CURSOR FOR SELECT DISTINCT TOP 100 PERCENT ' + QUOTENAME(@namefield) + ' FROM ' + QUOTENAME(@table) + ' WHERE ' + QUOTENAME(@basefield) + ' IS NOT NULL ORDER BY ' + QUOTENAME(@namefield) + ' OPEN cols FETCH NEXT FROM cols INTO @colName WHILE @@FETCH_STATUS = 0 BEGIN SET @sqlBegin = @sqlBegin + '', t'' + CAST(@counter AS VARCHAR) + ''.'' + ''' + QUOTENAME(@valuefield) + ''' + '' AS '' + QUOTENAME(@colName) + '''' SET @sqlMiddle = @sqlMiddle + '' LEFT OUTER JOIN '' + ''' + QUOTENAME(@table) + ''' + '' AS t'' + CAST(@counter AS VARCHAR) + '' ON t0.'' + ''' + QUOTENAME(@basefield) + ''' + '' = t'' + CAST(@counter AS VARCHAR) + ''.'' + ''' + QUOTENAME(@basefield) + ''' + '' AND t'' + CAST(@counter AS VARCHAR) + ''.'' + ''' + QUOTENAME(@namefield) + ''' + ''='' + QUOTENAME(@colName, '''''''') + '''' SET @counter = @counter + 1 FETCH NEXT FROM cols INTO @colName END CLOSE cols DEALLOCATE cols SET @sqlEnd = '' WHERE t0.'' + ''' + QUOTENAME(@basefield) + ''' + '' IS NOT NULL'' DECLARE @sql NVARCHAR(MAX) SET @sql = @sqlBegin + @sqlMiddle + @sqlEnd EXEC sp_executesql @sql' EXEC sp_executesql @sql RETURN 0 GO CREATE TABLE test_data ( id int identity primary key, person_id int, person_data_field VARCHAR (128), person_data_value VARCHAR (128) ) GO INSERT INTO dbo.test_data (person_id, person_data_field, person_data_value) VALUES (1, 'Name', 'John') INSERT INTO dbo.test_data (person_id, person_data_field, person_data_value) VALUES (1, 'Surname', 'Smith') INSERT INTO dbo.test_data (person_id, person_data_field, person_data_value) VALUES (1, 'Email', ' [e-mail] ') INSERT INTO dbo.test_data (person_id, person_data_field, person_data_value) VALUES (2, 'Name', 'Sarah') INSERT INTO dbo.test_data (person_id, person_data_field, person_data_value) VALUES (2, 'Surname', 'Lee') INSERT INTO dbo.test_data (person_id, person_data_field, person_data_value) VALUES (2, 'Phone', '012345678') GO EXEC [dbo].[PivotData] @table='test_data', @basefield='person_id', @namefield='person_data_field', @valuefield='person_data_value' GO 

I realized that this was working too slowly and stopped setting it further, but that seems to be your requirement.