TSQL从两个表创建一个动态报告，一个表保存标题，另一个表保存数据 [英] TSQL creating a dynamic report from two tables, one table is holds the headers, other one, data

问题描述

想象一下一种情况，我想基于[ReportItems]的[Title]列作为标题从[FormValues]中获取动态报告作为数据.

Imagine a scenario in which I want to get a dynamic report from [FormValues] as data, based on [Title] column of [ReportItems] as header.

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

I'm really confused how to do it and tried many ways, but none of them work fine.

我应该能够给程序一个[ReportID]并得到结果.

I should be able to give a procedure a [ReportID] and get the result.

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

[FormID] and [FieldID] are relational keys in between two tables.

任何帮助将不胜感激.

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

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

And I want such a report as result:

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

推荐答案

要获得所需的结果，您将需要使用

To get the result that you want, you will need to use the PIVOT function.

如果提前知道所有值(title)，则可以对静态查询进行硬编码:

If all of your values (title) are known ahead of time, then you can hard-code a static query:

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提琴.

但是听起来您要转换成列的titles数量未知.如果真是这样，那么您将需要使用动态sql:

But it sounds like you will have an unknown number of titles that you want to turn into columns. If that is the case, then you will want to use dynamic 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提琴

两者的结果将是:

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

如果您有特定的SortOrder并将其存储在表中，那么在获取列列表时，将使用以下内容，并将以正确的顺序返回列: /p>

If you have a specific SortOrder that you need and you have it stored in a table, then when you are getting your list of columns, you will use the following and it will return the columns in the correct order:

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提琴

这篇关于TSQL从两个表创建一个动态报告，一个表保存标题，另一个表保存数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！