Results 1 to 9 of 9

Thread: Flatten File

  1. #1
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117

    Unanswered: Flatten File

    I try to flatten a table.

    in Access I would do this: iif(sequence=1,[Technology],Null) AS Tech1, iif(sequence=2,[Technology],Null) AS Tech2 FROM tblTechnologies GROUP BY Application

    How do I flatten a file in SQL Server?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    look up CASE in Books OnLine -- it does the same as the access IIF

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    Great, Thanks. Step 1 completed.

    Now I try to group the records so they all show up as 1 record with the primary key, but SQL want me to show list all fields i.e. GROUP BY itemID, sequence,Technology
    In this case the fields will never show on one line.

    Any suggestions?
    Thanks..

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Hi Torgue

    I just have a question. How can many records be grouped together and shown to all have the same Primary Key? If five records are grouped together (on sequence,Technology) each with there own Primary Key (itemID), which one of the five (itemID) do you choose to show when grouping?
    MCDBA

  5. #5
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    I'm sorry for the confusion. It is actually the Foreigh Key on the N-end of a 1:N relationship. The main table lists the applications and the N-side lists the technologies used for the application development.

    In an Excel export I need to display the application with all technologies.

    Like:
    Application1 Tech1 Tech2 Tech2
    Application2 Tech1 Tech2 Tech3

    Thx..

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    crosstab

    why don't you define the sql/server table in access as a linked table via odbc, and then write a crosstab query in access? i think you can easily export that to excel, too

    in any case, if you do want to run this right in sql/server, you need
    Code:
    select application
       , case when sequence=1 
          then technology else null end   as tech1
       , case when sequence=2
          then technology else null end   as tech2
     from ...
    or something similar

    rudy

  7. #7
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    Thanks very much for your help.

    Unfortunately I am using SQL Server as a web Backend, so I need to stick to SQL Server.

    I will see what other solutions are available to me. Perhaps I can work with codes (numbers) and then do a sum to group, then link this code to a lookup table.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    I will see what other solutions are available to me. Perhaps I can work with codes (numbers) and then do a sum to group, then link this code to a lookup table.
    if you have already tried my CASE example, you will notice that each of the 1:N rows is present, and i apologize for not having shown how to summarize
    Code:
    select application
       , max( case when sequence=1 
          then technology else '' end )   as tech1
       , max( case when sequence=2
          then technology else '' end )   as tech2
     from ...
    group by application
    the problem is, i don't know if you are doing any summing (is "technology" a char field?)

    if you still cannot figure it out, please show your two table layouts

    rudy

  9. #9
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    works great, thank you very much.

    technology is a character field, but the max takes the populated fields over the null fields.

Posting Permissions

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