Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
sql query question

Answered sql query question

  • Wednesday, March 14, 2012 6:16 PM
     
     
    Sign In to Vote
    0

    Very very new to sql and working with existing database that I cannot change. I've worked written a query that pulls information from several tables to display Customer details, Product Name and quantity purchased. The only issue I have is that if the same customer buys two different products, they show as two separate rows on the resulting csv file and I need to be able to display it differently. Is it possible to display the cell data for a table column as a column heading in a query result? So for example my Product table column format is this:

    and I want to display the results of a query so that each of the products are displayed as column headings with the number of sales of each product below - can this be done?

     

All Replies

  • Wednesday, March 14, 2012 6:19 PM
     
     Proposed
    Sign In to Vote
    0

    You can try using PIVOT clause.

    http://msdn.microsoft.com/en-us/library/ms177410.aspx


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

     
  • Wednesday, March 14, 2012 7:01 PM
    Moderator
     
     Proposed
    Sign In to Vote
    0
    You will need to pivot the rows to columns.  Here is a blog post with a sample of how to do that, http://jahaines.blogspot.com/2009/08/pivoting-data-using-sql-2005-part-1.html

    http://jahaines.blogspot.com/

     
  • Thursday, March 15, 2012 1:06 PM
     
      Has Code
    Sign In to Vote
    0

    Thanks for both of these, had a look at both the msdn link and your blogspot Adam but I think I'm a bit too much of a beginner to get the hang of this as I keep getting syntax errors when I try it.

    I stripped it back to basics (I think) to try get just the one column to pivot this but still getting Your SQL is invalid: Incorrect syntax near the keyword 'FOR'.

    For this query:

    SELECT OrderDetails.ProductCode, [BLOGBK] AS BLOGBK, [BLOGBK2] AS BLOGBK2, [DTFSBK1] AS DTFSBK1, [ENLBOX1] AS ENLBOX1, [ENLBOX2] AS ENLBOX2, [ENTS-LG] AS ENTS_LG, [ENTS-MED] AS ENTS_MED, [ENTS-XL] AS ENTS_XL, [GYPBK] AS GYPBK, [ICTS1CD] AS ICTS1CD, [ICTS2DVDN] AS ICTS2DVDN, [ICTS2DVDP] AS ICTSDVDP, [ICTSAB] AS ICTSAB, [ICTSABPACK] AS ICTSBPACK, [ICTSBK1] AS ICTSBK1, [ICTSBNTSC] AS ICTSBNTSC, [ICTSBPAL] AS ICTSBPAL, [THOTNTSC1] AS THOTNTSC1, [THOTPAL1] AS THOTPAL1, [TWU2DVDN] AS TWU2DVDN, [TWU2DVDP] AS TWU2DVDP, [TWUAB] AS TWUAB, [TWUBK1] AS TWUBK1, [TWUBNTSC] AS TWUBNTSC, [TWUBPAL] AS TWUBPAL
    FROM OrderDetails 
    PIVOT (FOR ProductCode IN [BLOGBK], [BLOGBK2], [DTFSBK1], [ENLBOX1], [ENLBOX2], [ENTS-LG], [ENTS-MED], [ENTS-XL], [GYPBK], [ICTS1CD], [ICTS2DVDN], [ICTS2DVDP], [ICTSAB], [ICTSABPACK], [ICTSBK1], [ICTSBNTSC], [ICTSBPAL], [THOTNTSC1], [THOTPAL1], [TWU2DVDN], [TWU2DVDP], [TWUAB], [TWUBK1], [TWUBNTSC], [TWUBPAL])
    AS Orders

    I've tried replacing ProductCode with OrderDetails.ProductCode as there are multiple tables in the database and ProductCode occurs on more than one but still get the same invalid statement - can you tell what I'm doing wrong?

     
  • Thursday, March 15, 2012 1:15 PM
     
     
    Sign In to Vote
    0

    Yes.

    You need an aggregate function in the PIVOT clause.

    PIVOT ( MAX(..) FOR PRODUCTCODE IN ...) AS Orders


    N 56°04'39.26"
    E 12°55'05.63"

     
  • Thursday, March 15, 2012 1:20 PM
     
     
    Sign In to Vote
    0

    Your Pivot statement needs to be something like:

    (MAX(OrderDetails) FOR ProductCode IN (...)) pvt (the actual aggregate might be different in your case).

    Also, it looks like you need to pivot on more than one column.  Take a look at http://blogs.msdn.com/b/kenobonn/archive/2009/03/22/pivot-on-two-or-more-fields-in-sql-server.aspx.

     
  • Thursday, March 15, 2012 5:07 PM
     
     
    Sign In to Vote
    0

    Thanks for those - still trying, will let you know if I succeed!!

     
  • Friday, March 16, 2012 1:36 AM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    Try:

    SELECT * FROM 
FROM (Select OrderID, Cost, ProductCode FROM OrderDetails ) X
PIVOT (Sum(Cost)  FOR ProductCode IN [BLOGBK], [BLOGBK2], [DTFSBK1], [ENLBOX1], [ENLBOX2], 
    [ENTS-LG], [ENTS-MED], [ENTS-XL], [GYPBK], [ICTS1CD], [ICTS2DVDN], [ICTS2DVDP], [ICTSAB], 
    [ICTSABPACK], [ICTSBK1], [ICTSBNTSC], [ICTSBPAL], [THOTNTSC1], [THOTPAL1], [TWU2DVDN], [TWU2DVDP], 
    [TWUAB], [TWUBK1], [TWUBNTSC], [TWUBPAL]))
AS Orders

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog