August 21, 2007 at 10:47 am
I have a table as follows:
id name value
1 name1 val1
1 name2 val2
1 name3 val1
2 name1 val2
3 name1 val1
and I need to turn it into
id name1 name2 name3
1 val1 val3 val3
2 val2
3 val1
when I try a dynamic crosstab as follows
select id, max(case when name = 'name1' then value else '' end) as name1,
max(case when name = 'name2' then value else '' end) as name2
max(case when name = 'name3' then value else '' end) as name3
FROM table group by id
I get an error stating 'Operand data type text is invalid for max operator.'
The same query works fine in mysql. I don't actually need the max value (as there is only one) i just need the only value.
Thanks in advance
August 21, 2007 at 10:53 am
ALTER the table and use VARCHAR(MAX) as datatype instead of TEXT.
N 56°04'39.16"
E 12°55'05.25"
August 21, 2007 at 11:10 am
thanks - worked like a charm
what other differences are there between varchar(MAX) and text?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply