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