I'm working on a database that must be extremely flexible as to the "width" of the table Person. Thus, I have put static fields in the Person table and have another table, PersonStat (statistics), for "the rest" of the fields (which might change upon every import of data). Then I make a view using the PIVOT command so that the user sees only a PersonPivot "table" through which to access data.
It all worked fine, but a problem has now arisen due to users' demand to select data of different types. Earlier all they got was varchar(128) and had to convert or cast the values themselves.
This is my structure:
Person table:
*ID int NOT NULL Identity
LastName varchar(30) NULL
FirstName varchar(30) NULL
...
PersonStat table:
*ID int NOT NULL Identity
PersonId int NOT NULL (FK -> Person.ID)
StatName varchar(30) NULL
StatValue_int int NULL
StatValue_bool bit NULL
StatValue_dec decimal NULL
StatValue_char varchar(256) NULL
StatValue_date smalldatetime NULL
ValueType varchar(4) NOT NULL
...
The PersonStat table thus holds data saying (for instance) that the value of "Sex" is "Male" and the value of "BirthDate" is "12/13/1975" and the value of "IsContact" is true, and the ValueType field tells me what datatype the value is. Thus, the user sees the PersonPivot view, which then looks something like this:
ID LastName FirstName Sex BirthDate IsContact
1 Bylund Per Male 12/13/1975 true
I'm creating (or updating) the view every time I get an import of new data to the Person database and execute the spCreatePersonPivot procedure (below) to use PIVOT to put data in the right places. However, when trying to put data of different types in the dynamic fields (the ones specified in the PersonStat table) the procedure crashes. I know why: it cannot implicitly convert all values to a single format. But it shouldn't - it should take whatever value there is and keep it in its current type.
I have no idea how to do this, even though I think I should "simply" move the CASE somehow to the PIVOT aggregate function. But how? Any help is greately appreciated.
DECLARE @Sql varchar(2000)
SET @Sql = 'CREATE VIEW PersonPivot AS ('
--Generate a list of columns that apply to this set of data
SELECT @Columns = COALESCE(@Columns + ', ', '') + '[' + RTrim(LTrim(tblPerson.StatName)) + ']'
FROM (SELECT DISTINCT StatName FROM PersonStat WHERE URN IN (SELECT DISTINCT URN FROM Person)) tblPerson
--Build the SQL statement.
SET @Sql = @Sql + ' SELECT T1.*, T2.' + REPLACE(@Columns, ', ', ', t2.') + '
FROM Person T1
JOIN (
SELECT * FROM (
SELECT URN, PersonStat.StatName, CASE ValueType
WHEN ''int'' THEN StatValue_int
WHEN ''bool'' THEN StatValue_bool
WHEN ''dec'' THEN StatValue_dec
WHEN ''char'' THEN StatValue_char
WHEN ''date'' THEN StatValue_date
END AS StatValue
FROM PersonStat
) P
PIVOT
(
MAX(StatValue)
FOR StatName IN (' + @columns + ')
) AS P
) T2
ON T1.URN = T2.URN )'
--Execute
EXEC(@Sql)
It all worked fine, but a problem has now arisen due to users' demand to select data of different types. Earlier all they got was varchar(128) and had to convert or cast the values themselves.
This is my structure:
Person table:
*ID int NOT NULL Identity
LastName varchar(30) NULL
FirstName varchar(30) NULL
...
PersonStat table:
*ID int NOT NULL Identity
PersonId int NOT NULL (FK -> Person.ID)
StatName varchar(30) NULL
StatValue_int int NULL
StatValue_bool bit NULL
StatValue_dec decimal NULL
StatValue_char varchar(256) NULL
StatValue_date smalldatetime NULL
ValueType varchar(4) NOT NULL
...
The PersonStat table thus holds data saying (for instance) that the value of "Sex" is "Male" and the value of "BirthDate" is "12/13/1975" and the value of "IsContact" is true, and the ValueType field tells me what datatype the value is. Thus, the user sees the PersonPivot view, which then looks something like this:
ID LastName FirstName Sex BirthDate IsContact
1 Bylund Per Male 12/13/1975 true
I'm creating (or updating) the view every time I get an import of new data to the Person database and execute the spCreatePersonPivot procedure (below) to use PIVOT to put data in the right places. However, when trying to put data of different types in the dynamic fields (the ones specified in the PersonStat table) the procedure crashes. I know why: it cannot implicitly convert all values to a single format. But it shouldn't - it should take whatever value there is and keep it in its current type.
I have no idea how to do this, even though I think I should "simply" move the CASE somehow to the PIVOT aggregate function. But how? Any help is greately appreciated.
DECLARE @Sql varchar(2000)
SET @Sql = 'CREATE VIEW PersonPivot AS ('
--Generate a list of columns that apply to this set of data
SELECT @Columns = COALESCE(@Columns + ', ', '') + '[' + RTrim(LTrim(tblPerson.StatName)) + ']'
FROM (SELECT DISTINCT StatName FROM PersonStat WHERE URN IN (SELECT DISTINCT URN FROM Person)) tblPerson
--Build the SQL statement.
SET @Sql = @Sql + ' SELECT T1.*, T2.' + REPLACE(@Columns, ', ', ', t2.') + '
FROM Person T1
JOIN (
SELECT * FROM (
SELECT URN, PersonStat.StatName, CASE ValueType
WHEN ''int'' THEN StatValue_int
WHEN ''bool'' THEN StatValue_bool
WHEN ''dec'' THEN StatValue_dec
WHEN ''char'' THEN StatValue_char
WHEN ''date'' THEN StatValue_date
END AS StatValue
FROM PersonStat
) P
PIVOT
(
MAX(StatValue)
FOR StatName IN (' + @columns + ')
) AS P
) T2
ON T1.URN = T2.URN )'
--Execute
EXEC(@Sql)
