Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004

    Unanswered: query to parse out values from one column into different columns

    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.

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    Look up CROSSTAB queries in Books Online.
    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
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Jun 2004
    Thanks for the info. I'll let you know how I do.

  4. #4
    Join Date
    Jun 2004
    I incorporated the crosstab query into my code and the performance is stellar. Thanks for your help. !!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts