query to parse out values from one column into different columns

jgags · #1 (**permalink**) 02-02-06, 15:08

I have a table where different types of values are stored in one field, but I need to seperate them into different fields based on a value in another field.

For (hypothetical) example:

There is an existing table with following info in three columns:
userid record recordtag
1 joe 1
1 j 2
1 jr 3
2 bob 1
2 a 2
2 sr 3
where recordtag indicates (1 for first name, 2 for middle initial, 3 for suffix)

I need to query these records for a report so it the output is:

userID firstname middleinitial suffix
1 joe j jr
2 bob a sr

What's the most efficient approach to create a query that will give me desired results? I have managed to create a very complex query that derives tables for each column I want to create and queries off of that derived table for the 'record' value based on the 'recordtag' values for a given 'userid'. The query is extremely slow, so I know there's some better way out there to get the results I want. Any help would be greatly appreciated. Thanks.

blindman · #2 (**permalink**) 02-02-06, 15:16

Look up CROSSTAB queries in Books Online.

Code:

select	userid,
	max(case recordtag when 1 then record end) as firstname,
	max(case recordtag when 2 then record end) as middleinitial,
	max(case recordtag when 3 then record end) as suffix
from	[YourTable]
group by userid

jgags · #3 (**permalink**) 02-02-06, 15:31

Thanks for the info. I'll let you know how I do.

jgags · #4 (**permalink**) 02-03-06, 10:01

I incorporated the crosstab query into my code and the performance is stellar. Thanks for your help. !!