Results 1 to 4 of 4

Thread: Reformat Data

  1. #1
    Join Date
    Mar 2003
    Posts
    2

    Unanswered: Reformat Data

    I have a problem in that I need to reformat my data.

    I have the data for part numbers to products from the selection.
    e.g.

    Part No Product Application
    001 aaaa Standard
    001 bbbb Option
    002 aaaa Option
    002 bbbb Standard
    003 aaaa Option

    and I wish reformat it into

    Part No aaaa bbbb
    001 Standard Option
    002 Option Standard
    003 Option Not App (Note missing from above selection)

    There could be several products with various parts applicable / not applicable

  2. #2
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    This may be a little crude, and I'm sure that someone can point out better ways to do it. However it does work with the limited example that you describe.

    The first part is just me building a temptable to select from

    Hope it helps,

    Brent

    Create Table #TempParts (PartNo int, parttype varchar(5), description varchar(10))
    insert #tempparts values(001, 'aaaa', 'standard')
    insert #tempparts values(001, 'bbbb', 'option')
    insert #tempparts values(002, 'bbbb', 'standard')
    insert #tempparts values(002, 'aaaa', 'option')
    insert #tempparts values(003, 'aaaa', 'option')


    select PartNo,
    Max(Case parttype when 'aaaa' then [description] end) as AAAA,
    Max(Case parttype when 'bbbb' then [description] end) as BBBB
    from #tempparts
    group by partno

  3. #3
    Join Date
    Mar 2003
    Posts
    2
    Thanks for your reply but the problem is when I do the query on part no's I don't know at that time to what selection of Products they are applicable, and unfortunately there are hundreds of possible products.

  4. #4
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Do you have an equally large amount of Part No's as Products? What about Applications? Just trying to get an idea of the variables that need to be arranged in some sort of pivot table.

Posting Permissions

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