TSQL creating a dynamic report from two tables, on

2019-07-14 23:37发布

问题:

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.

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

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

Any kind help would be highly appreciated.

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

回答1:

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

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;

See SQL Fiddle with Demo.

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)

See SQL Fiddle with Demo

The result for both would be:

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

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,'')

See SQL Fiddle with Demo



回答2:

DECLARE @SQL NVARCHAR(MAX)

SET @SQL='SELECT F.UserName'
SELECT @SQL = @SQL+', MAX(CASE WHEN FieldID='''+CONVERT(VARCHAR(50), FieldID)+''' THEN F.Value END) AS ['+Title+']
'
FROM #ReportItems
SET @SQL = @SQL+' FROM #FormValues F GROUP BY F.UserName ORDER BY 1'
--select @sql
EXEC sp_ExecuteSQL @SQL

Later edit: procedure for report based on Report ID an sort columns

CREATE PROCEDURE spReport
@ReportID uniqueidentifier,
@SortColumns NVARCHAR(MAX) --shoud be a comma separated list of ReportItems.Title
AS 
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL='SELECT F.UserName'
    SELECT @SQL = @SQL+', MAX(CASE WHEN F.FieldID='''+CONVERT(VARCHAR(50), FieldID)+''' THEN F.Value END) AS ['+Title+']
    '
    FROM ReportItems
    WHERE ReportID=@ReportID --create the dynamic sql only for the items in your report 

    SET @SQL = @SQL+' FROM FormValues F 
    JOIN ReportItems R ON F.FormID=R.FormID
    WHERE R.ReportID = @ReportID
    GROUP BY F.UserName '
    IF @SortColumns<>''
        SET @SQL = @SQL + 'ORDER BY '+@SortColumns -- beware of SQL injection. 
    select @sql
    EXEC sp_ExecuteSQL @SQL, N'@ReportID uniqueidentifier', @ReportID=@ReportID
END

But, and I can't stress this enough, you have to pay special attention to then @SortColumns parameter, because you're opening yourself to SQL Injection.