I've reorganized some tables in my database to be more flexible but I'm not really sure how to write the SQL to extract meaningful data from them.
I have the following tables (somewhat abbreviated for a clearer example):
CREATE TABLE Loans(
Id int,
SchemaId int,
LoanNumber nvarchar(100)
);
CREATE TABLE SchemaFields(
Id int,
SchemaId int,
FieldName nvarchar(255)
);
CREATE TABLE LoanFields(
Id int,
LoanId int,
SchemaFieldId int,
FieldValue nvarchar(4000)
);
With the following data:
INSERT INTO Loans (Id, SchemaId, LoanNumber) VALUES (1, 1, 'ABC123');
INSERT INTO SchemaFields (Id, SchemaId, FieldName) VALUES (1, 1, 'First Name');
INSERT INTO SchemaFields (Id, SchemaId, FieldName) VALUES (2, 1, 'Last Name');
INSERT INTO LoanFields (Id, LoanId, SchemaFieldId, FieldValue) VALUES (1, 1, 1, 'John');
INSERT INTO LoanFields (Id, LoanId, SchemaFieldId, FieldValue) VALUES (2, 1, 2, 'Doe');
The objective is to get a query that is flat for a loan with all its fields. (In the real world there will likely be between 20-30 fields for the same schema, but we just have 2 in the example):
LoanNumber First Name Last Name
---------- ----------- ----------
ABC123 John Doe
I cannot use a pivot that references the 'First Name' and 'Last Name' because I'll have no idea what will actually be there.
I have a SQL Fiddle here with schema already in place.
How can I get the desired result?
Best Answer
This can be done using the PIVOT function, but since it sounds like you want to change the query based on the schemaId, then you will want to use dynamic SQL.
If you had a known number of values or knew the columns for a specific schemaID, then you could hard-coded the query. A static query would be:
See SQL Fiddle with Demo.
If you had an unknown number or you want the columns to change based on a
SchemaId
that you are passing into a procedure, then you will use dynamic SQL to generate the SQL string:See SQL Fiddle with Demo. Both of these queries will generate the result: