登入
Microsoft.com
 
Microsoft
 
 
 

none
Combine multiple records (rows) into 1 record (row) - Access RRS feed

 > 

  • 問題

  • Question
    登入以投票
    0
    登入以投票
    Now, I have the a table, like the following, in Access.

    Shop     ProductType   ProductName      Price
    A              Fruit                Apple             $6
    A            Vegetable          Leek               $3
    B              Fruit               Orange            $5
    B             Meat                 Pork              $10
    B            Vegetable         Tomato            $3

    How can I combine different rows for the same Shop into a single row, which is shown as the following format using query?
    Shop     ProductType1   ProductName1      Price1     Product Type2     ProductName2    Price2         Product TypeOther     ProductNameOther    PriceOther
    A              Fruit                Apple                    $6           Vegetable          Leek               $3
    B              Fruit               Orange                   $5           Vegetable         Tomato            $3                           Meat                 Pork                         $10                   


    2009年11月10日 上午 04:55
    |

所有回覆

  • Question
    登入以投票
    0
    登入以投票

    For me, I will write sample code to create a new Access

    And then fill in values by your own need~~~
    :D

     

    MCPD .Net, CCNP Love programming, but not an IT Guy Haha! :D
    2010年5月12日 上午 03:56
    |
  • Question
    登入以投票
    0
    登入以投票

    Hi,

    Please try the below query. If your requirment is to show only fix number of columns for producttype,productname,price (as per below example 3 producttype,productname,price), then you can use the below query. Please note that in <select shop, producttype, productname, price, ROW_NUMBER()  over(partition by shop order by producttype) as seq>, the seq field is extracted to get the running number within the shop and the seq field is used to generate the column sets.

    select shop, max(CASE WHEN seq='1' THEN ProductType END) as [1],max(CASE WHEN seq='1' THEN ProductName END) as [1],max(CASE WHEN seq='1' THEN price END) as [1], 
max(CASE WHEN seq='2' THEN ProductType END) as [2],max(CASE WHEN seq='2' THEN ProductName END) as [2], max(CASE WHEN seq='2' THEN price END) as [2], 
max(CASE WHEN seq='3' THEN ProductType END) as [3],max(CASE WHEN seq='3' THEN ProductName END) as [3],max(CASE WHEN seq='3' THEN price END) as [3] 
from (select shop,ProductType,ProductName,price,ROW_NUMBER() over(partition by shop order by ProductType) as seq from prodsforpivot) A GROUP BY shop

    To dynamically generate the columns based on tables' contents, you can use an excellent stored procedure (dynamic cross tab) written by Jeff at http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

    Please check if this helps and feel free to discuss should any further help required.

    Mohamed Irshad

     

    2010年7月29日 上午 08:57
    |