January 24, 2007 at 8:33 am
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)
January 29, 2007 at 8:00 am
This was removed by the editor as SPAM
January 29, 2007 at 9:59 am
I'm a little confused. What is it that you're trying to accomplish in the end?
January 29, 2007 at 10:06 am
I'm trying to accomplish a view that offers a unified view of the available data, and that is very dynamic: it should vary in "width" (number of fields) as needs change. Hence, when a new variable is added I want the view to reflect this change through supplying this new variable as a "standard" field accessable from the view. The main purpose is dynamicism.
But never mind, I'm trying another approach.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply