TSQL从两个表创建一个动态报告，一个表保存标题，另一个表保存数据

admin

TSQL从两个表创建一个动态报告，一个表保存标题，另一个表保存数据

sql

想象一个场景，我想[FormValues]基于as标题的[Title]列从as数据中获取动态报告[ReportItems]。

我真的很困惑如何做，尝试了很多方法，但是没有一个能很好地工作。

我应该可以给一个过程一个[ReportID]并得到结果。

[FormID]和[FieldID]是两个表之间的关系键。

任何帮助将不胜感激。

CREATE TABLE #ReportItems(
    ReportItemID [uniqueidentifier] NOT NULL primary key,
    ReportID [uniqueidentifier] NOT NULL,
    FormID [uniqueidentifier] NOT NULL,
    FieldID [uniqueidentifier] NOT NULL,
    Title nvarchar(100) NOT NULL
) 
GO

insert into #ReportItems
select '5674d274-b146-4251-be0d-a15000e7cefa', '597d37c0-563b-42f0-99be-a15000dc7a65', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7a', 'First Name'
insert into #ReportItems
select '5674d274-b146-4252-be0d-a15000e7cefa', '597d37c0-563b-42f0-99be-a15000dc7a65', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7b', 'Last Name'
insert into #ReportItems
select '5674d274-b146-4253-be0d-a15000e7cefa', '597d37c0-563b-42f0-99be-a15000dc7a65', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7c', 'Age'
GO

CREATE TABLE #FormValues(
    ValueID uniqueidentifier NOT NULL primary key,
    FormID uniqueidentifier NULL,
    FieldID uniqueidentifier NOT NULL,
    UserName nvarchar(100) NOT NULL,
    Value nvarchar(max) null
)
GO

insert into #FormValues
select 'af6dc400-3972-49ff-9711-a1520002359e', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7a', 'user 1', 'Mike'
insert into #FormValues
select 'af6dc400-3972-49ff-9721-a1520002359e', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7b', 'user 1', 'Oscar'
insert into #FormValues
select 'af6dc400-3972-49ff-9731-a1520002359e', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7c', 'user 1', '20'

insert into #FormValues
select 'af6dc400-3972-49ff-9741-a1520002359e', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7a', 'user 2', 'Merry'
insert into #FormValues
select 'af6dc400-3972-49ff-9761-a1520002359e', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7c', 'user 2', '23'

insert into #FormValues
select 'af6dc400-3972-49ff-9771-a1520002359e', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7a', 'user 3', 'Alen'
insert into #FormValues
select 'af6dc400-3972-49ff-9781-a1520002359e', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7b', 'user 3', 'Escott'
insert into #FormValues
select 'af6dc400-3972-49ff-9791-a1520002359e', '01304636-fabe-4a3e-9487-a14b012f9a61', 'ba6b9b1a-92ef-4905-830a-a15000d05f7c', 'user 3', '28'
GO

Select * from #ReportItems
Select * from #FormValues
GO

我想要这样的报告作为结果：

User Name   |   First Name  |   Last Name   |   Age
User 1      |   Mike        |   Oscar       |   20
User 2      |   Merry       |               |   23
User 3      |   Alen        |   Escott      |   28
User n      |   ...         |   ...         |   ...



drop table #ReportItems
drop table #FormValues

阅读 140

2021-06-07

共1个答案

admin

要获得所需的结果，您将需要使用该PIVOT函数。

如果您title提前知道了所有值（），则可以对静态查询进行硬编码：

select *
from
(
    select r.Title, f.UserName, f.Value
    from ReportItems r
    left join FormValues f
        on r.FormID = f.FormID
        and r.FieldID = f.FieldID
) src
pivot
(
    max(value)
    for title in ([First Name], [Last Name], Age)
) piv;

请参阅带有演示的SQL Fiddle。

但是听起来titles您想将其变成列的数量未知。如果真是这样，那么您将需要使用动态sql：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Title) 
                    from ReportItems
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT UserName,' + @cols + ' from 
             (
                select r.Title, f.UserName, f.Value
                from ReportItems r
                left join FormValues f
                    on r.FormID = f.FormID
                    and r.FieldID = f.FieldID
            ) x
            pivot 
            (
                max(value)
                for Title in (' + @cols + ')
            ) p '

execute(@query)

参见带有演示的SQL Fiddle

两者的结果将是：

| USERNAME | FIRST NAME | LAST NAME | AGE |
-------------------------------------------
|   user 1 |       Mike |     Oscar |  20 |
|   user 2 |      Merry |    (null) |  23 |
|   user 3 |       Alen |    Escott |  28 |

如果您有需要的特定信息SortOrder，并且已将其存储在表中，那么在获取列列表时，将使用以下内容，并将以正确的顺序返回这些列：

select @cols = STUFF((SELECT ',' + QUOTENAME(Title) 
                    from ReportItems
                    group by Title, sortorder
                    order by sortorder
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

参见带有演示的SQL Fiddle

2021-06-07