SQL2005中的动态数据透视所需的帮助 [英] Help needed with Dynamic Pivoting in SQL2005
问题描述
我有一个名称/值对表,用于存储标签:
I have a table of name value pairs where I am storing tags:
TAGID | NAME | VALUE
我有一张此标签适用于事物"的表
I have a table of 'Things' this tags apply to
THINGID | TAGID
我需要一个查询来生成一个resultSet,其中列/字段都是给定THINGID的所有可能的TAG-NAMES(TAG表中的NAME字段),并且值是对应的标签值.
I need a query to generate a resultSet were the columns/fields are all possible TAG-NAMES (NAME field in the TAG table)for a given THINGID and the values are the correspondent tag values.
THINGID | TAGNAME1 | TAGNAME2 | ... |etc.
我可以找到固定列的示例,但没有类似的内容.
I can find examples with fixed columns but nothing like this.
推荐答案
有些相似,但架构略有不同(thing_id,tag_name,tag_value)-没有标签表:
Something similar with slightly different schema (thing_id, tag_name, tag_value) - without tags 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', 'John@Smith.com')
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 figured out that this works way too slow and stopped tuning it further, but it sort of works up to your requirement.
这篇关于SQL2005中的动态数据透视所需的帮助的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!