2 Replies Latest reply: Nov 9, 2012 2:20 PM by Voltaire RSS

    need a query to pivot the data

    727914
      Hi,

      The following query gives the dealer_name, each customer_vin (like a customer_id) and the number of services done for that customer_vin.

      select d.name delaer_name,
      c.vin customer_vin,
      count(s.servicerecord_id) services#
      from ford.customers c,
      ford.dealers d,
      ford.servicerecords s
      where c.dealer_id = d.dealer_id
      and s.customer_id = c.customer_id
      group by d.name, c.vin

      NAME     VIN     SERVICES#
      Pat Milliken Ford Inc.     1FMDK03W9     1
      Long-Lewis Ford Lincoln     1FMHK7D83     3
      Long-Lewis Ford Lincoln     1FMHK7D81     3
      Sawgrass Ford     8FMDK3KC5C     4
      Sawgrass Ford     1FT7X2B64C 4
      Sawgrass Ford     2FMDK3ABCD     4
      Sawgrass Ford     3247X2B65C 4

      Could you give me a query to pivot the data as below

      SERVICES# Pat Milliken Ford Inc Long-Lewis Ford Lincoln Sawgrass Ford
      1 1(# of cust vins) 0(# of cust vins) 0(# of cust vins)
      3 0(# of cust vins) 2(# of cust vins) 0(# of cust vins)
      4 0(# of cust vins) 0(# of cust vins) 4(# of cust vins)

      Thank you very much.
        • 1. Re: need a query to pivot the data
          Srini VEERAVALLI
          Check these links
          SQL Server
          http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

          Oracle
          http://www.dba-oracle.com/t_display_multiple_column_values_same_rows.htm
          http://www.dba-oracle.com/t_pivot_examples.htm
          http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php

          Appreciate if you mark as correct/helpful
          • 2. Re: need a query to pivot the data
            Voltaire
            Hi,

            Can you try this:

            With data1 As
            ( select d.name delaer_name,
            c.vin customer_vin,
            s.servicerecord_id services
            from ford.customers c,
            ford.dealers d,
            ford.servicerecords s
            where c.dealer_id = d.dealer_id
            And S.Customer_Id = C.Customer_Id
            group by d.name, c.vin
            )
            SELECT * FROM data1 PIVOT ( count(services) FOR delaer_name IN ( select delaer_name from ford.dealers ) )

            Regards,
            Jay