How do I split a database column in to separate, query-able values?

Question

I have no idea how to do this query. I need to grab a bunch of information, but it's all in one column. If the table rotated 90 degreses to the right it'd be just fine... lol...

The ObjectInternalID column represents one event.
Conditions
I want all event(s) details(Values Column)
only for one person where values = "his name"
within a specific date range where values > "date range"

Any idea what the easiest way to do this is?

PROGRESS UPDATE

I'm trying to create a temp table out of this, but I get issues when I add the into statement.

select 
    s.ObjectInternalID
    , MAX(Case when UserDefinedFieldInternalID = 3 then Value end) as date1
    , MAX(Case when UserDefinedFieldInternalID = 4 then Value end) as resident1
    , MAX(Case when UserDefinedFieldInternalID = 66 then Value end) as attending1
    , MAX(Case when UserDefinedFieldInternalID = 14 then Value end) as date2
into 
    #TempQueryTable(
     MAX(Case when UserDefinedFieldInternalID = 3 then Value end) as date1
    , MAX(Case when UserDefinedFieldInternalID = 4 then Value end) as resident1
    , MAX(Case when UserDefinedFieldInternalID = 66 then Value end) as attending1
    , MAX(Case when UserDefinedFieldInternalID = 14 then Value end) as date2
from 
    DocUserDefinedData s
group by 
    s.ObjectInternalID

If I comment out the into section, it runs, but otherwise I get an "incorrect syntac ',' error. I don't see

Answer 1

You could create a view on the table to do the split - it looks as though the field UserDefinedFieldInternalID and the content of the field has a strong correlation, at least in terms of datatype - so, assuming you want to extract for a finite set of UserDefinedFieldInternalID's you could construct a view something like:

select d2.value, d14.value from thistable d2 join thistable d14 on d2.internalobjectid = d14.internalobjectid
where d2.value = 'his name' and
d2.UserDefinedFieldInternalID = 2 and
d14.UserDefinedFieldInternalID = 14 and
d14.value > "date range"

I'd suspect that somewhere else, there's a reference table where you'll find UserDefinedFieldInternalID joining with a list of types, so you might be able to bring that in to tidy things up a bit.

This way of representing data is perfectly valid in applications where the users can in some ways define their own schemas - it doesn't scale all that well, but in small/boutique implementations, that's not a problem (at least not for the first few months/years)

[edit] To converge a little with the layout of the other answer - here's another way of writing the code above - I've changed the joins to left joins so that if there aren't values matching, it just leaves the resulting value blank.

create view user_schema_query_view1 as
select 
s.ObjectInternalID
d3.value as date1,
d4.value as resident1,
d66.value as attending1,
d14.value as date2, 
from
DocUserDefinedData s 
left join DocUserDefinedData d3 on s.ObjectInternalID = d3.ObjectInternalID and 
d3.ObjectInternalID = 3
left join DocUserDefinedData d4 on s.ObjectInternalID = d4.ObjectInternalID and 
d3.ObjectInternalID = 4
left join DocUserDefinedData d66 on s.ObjectInternalID = d66.ObjectInternalID and 
d3.ObjectInternalID = 66
left join DocUserDefinedData d14 on s.ObjectInternalID = d14.ObjectInternalID and 
d3.ObjectInternalID = 14

Then, you can query the view using a statement like:

select * from user_schema_query_view1
where date1 = "some-date-value" and attending1 = "something else"

Try this out first for the structure, and then, if it suits, you could try working on casting the data into more helpful data types.

Answer 2

This is pretty simple using a cross tab, also known as conditional aggregation. It would be something like this.

select s.ObjectInternalID
    , MAX(Case when UserDefinedFieldInternalID = 3 then Value end) as YourDateColumn
    , MAX(Case when UserDefinedFieldInternalID = 4 then Value end) as YourFirstSmudgedColumn
    , MAX(Case when UserDefinedFieldInternalID = 2 then Value end) as YourColumnThatMightBeAName
    , MAX(Case when UserDefinedFieldInternalID = 66 then Value end) as YourSecondSmudgedColumn
    , MAX(Case when UserDefinedFieldInternalID = 14 then Value end) as YourSecondDateColumn
from SomeTable s
group by s.ObjectInternalID

If you need to add some filtering to this you can easily turn this into a derived table.

select *
from
(
    select s.ObjectInternalID
        , MAX(Case when UserDefinedFieldInternalID = 3 then Value end) as YourDateColumn
        , MAX(Case when UserDefinedFieldInternalID = 4 then Value end) as YourFirstSmudgedColumn
        , MAX(Case when UserDefinedFieldInternalID = 2 then Value end) as YourColumnThatMightBeAName
        , MAX(Case when UserDefinedFieldInternalID = 66 then Value end) as YourSecondSmudgedColumn
        , MAX(Case when UserDefinedFieldInternalID = 14 then Value end) as YourSecondDateColumn
    from SomeTable s
    group by s.ObjectInternalID
)
where YourColumnThatMightBeAName = 'SomeValueToFind'

Keep in mind that since this is an EAV you will have to cast/convert the values when they are a different datatype.

Where convert(datetime, YourDateColumn) <= '20160225'

If you need to insert into a temp table you should consider converting your string data into a datatype appropriate for the contents. Here is the syntax and how you would convert the two datetime values.

select s.ObjectInternalID
    , convert(datetime, MAX(Case when UserDefinedFieldInternalID = 3 then Value end)) as YourDateColumn
    , MAX(Case when UserDefinedFieldInternalID = 4 then Value end) as YourFirstSmudgedColumn
    , MAX(Case when UserDefinedFieldInternalID = 2 then Value end) as YourColumnThatMightBeAName
    , MAX(Case when UserDefinedFieldInternalID = 66 then Value end) as YourSecondSmudgedColumn
    , convert(datetime, MAX(Case when UserDefinedFieldInternalID = 14 then Value end)) as YourSecondDateColumn
INTO #SomeTempTable
from SomeTable s
group by s.ObjectInternalID