SQL Server 2005 中的水平到垂直表转换

Question

我有一个页面，可以从表中动态构建表单输入字段，允许用户将输入输入到表单中。每个输入都是 _info 表中的一个字段。

我想要在一行中显示用户的所有条目的报告。每个字段条目都是一行。所以我需要获取垂直数据并将其水平化。有许多不同的形式和不同的配置，因此必须是动态的。我读过很多关于交叉表数据透视的文章，但它们只适用于聚合/求和。

Registration_Info 表对于一份表单提交有多个条目。我需要列出这些水平，并将每个 form_field.label 作为其字段名称以进行报告。

下面是一个表格布局示例：

FORM
——————————————
FORM_ID|FORM_NAME
500|“Custom Form 1″

FORM_FIELD
——————————————
FORM_FIELD_ID|FORM_ID|LABEL
1|500|“Field 1″
2|500|“Field 2″

REGISTRATION
——————————————
REG_ID|FORM_ID|USER_ID
23|500|45
24|500|98

REGISTRATION_INFO
——————————————
REG_ID|FORM_FIELD_ID|FIELD_VALUE
23|1|“My Name”
24|2|“My City”

Required Report Output:
REG_ID |Field 1 |Field 2 |FORM_ID |USER_ID
———————————————————————————————
23 |My Name |My City |500 |45
24 |Another Name |Another City |500 |98 

Answer 1

我相信这会解决您的需求：

STATIC PIVOT

create table #form
(
    form_id int,
    form_name varchar(50)
)
create table #form_field
(
    form_field_id int,
    form_id int,
    label varchar(50)
)
create table #registration
(
    reg_id int,
    form_id int,
    userid int
)
create table #registration_info
(
    reg_id int,
    form_field_id int,
    field_value varchar(50)
)

insert into #form values(500, 'Custom Form 1')
insert into #form_field values(1, 500, 'Field 1')
insert into #form_field values(2, 500, 'Field 2')
insert into #registration values(23, 500, 45)
insert into #registration values(24, 500, 98)
insert into #registration_info values(23, 1, 'My Name')
insert into #registration_info values(24, 2, 'My City')


select r.reg_id
    , IsNull(p.[Field 1], '') as Field1
    , IsNull(p.[Field 2], '') as Field2
    , r.form_id
    , r.userid
from 
(
    select ri.reg_id
        , ri.field_value
        , ff.form_id
        , ff.label
    from #registration_info ri
    inner join #form_field ff
        on ri.form_field_id = ff.form_field_id
) riff
pivot 
(
    max(field_value)
    for label in ([Field 1], [Field 2])
) p
JOIN #registration r
    on p.form_id = r.form_id
    AND p.reg_id = r.reg_id

这是一个sqlfiddle 具有工作版本。

动态枢轴
此版本将首先获取字段列表，然后对它们进行透视。

create table form
(
    form_id int,
    form_name varchar(50)
)
create table form_field
(
    form_field_id int,
    form_id int,
    label varchar(50)
)
create table registration
(
    reg_id int,
    form_id int,
    userid int
)
create table registration_info
(
    reg_id int,
    form_field_id int,
    field_value varchar(50)
)

insert into form values(500, 'Custom Form 1')
insert into form_field values(1, 500, 'Field 1')
insert into form_field values(2, 500, 'Field 2')
insert into registration values(23, 500, 45)
insert into registration values(24, 500, 98)
insert into registration_info values(23, 1, 'My Name')
insert into registration_info values(24, 2, 'My City')

DECLARE @cols AS VARCHAR(MAX),
    @query  AS VARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.label) 
            FROM dbo.form_field c
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT r.reg_id, ' + @cols + ', r.form_id, r.userid from 
            (
                select ri.reg_id
                    , ri.field_value
                    , ff.form_id
                    , ff.label
                from registration_info ri
                inner join form_field ff
                    on ri.form_field_id = ff.form_field_id
           ) riff
            pivot 
            (
                 max(field_value)
                for label in (' + @cols + ')
            ) p 
            JOIN registration r
                on p.form_id = r.form_id
                AND p.reg_id = r.reg_id'


execute(@query)