pls provide the query for given below in sqlserver2005

Posted on 2011-04-25
Last Modified: 2012-06-27

l need sql query following table

quarter  sales
q1       1000
q1       2000
q1       3000
q1       4000
q2       5000
q2       6000
q2       7000
q2       8000
q3       1000
q3       2000
q3       3000
q3       4000
q4       5000
q4       6000
q4       7000
q4       8000
i want the output format like
q1    q2    q3    q4
1000  5000  1000  5000
2000  6000  2000  6000
3000  7000  3000  7000
4000  8000  4000  8000
Question by:mu_ravi1
    LVL 16

    Accepted Solution

    Please use following:

    select * from Yourtable
    pivot (Sum (sales) for quarter in ([q1],[q2],[q3],[q4])) as TotalSalesPerQuater

    for other details please follow:

    LVL 39

    Assisted Solution

    by:Pratima Pharande
    SELECT q1,q2,q3,q4
    (SELECT SomeName,sales
    FROM #SomeTable) AS pivTemp
    (   sales
        FOR SomeName IN (q1,q2,q3,q4)
    ) AS pivTable
    LVL 21

    Expert Comment

    by:Alpesh Patel
    Hi Use Pivot TAble to achieve solution.
    LVL 50

    Assisted Solution

    no more like this ... he doesn't want an accumulation just a list...

    ; with cte as (select sales,quarter
                                     ,row_number() over (partition by quarter order by sales) as rn
                               from yourtable)
     , cte1 as (select a.sales as s1
                             ,b.sales as s2
                             ,coalesce(a.rn,b.rn) as rn
                             from (select * from cte where quarter='q1') as a
                               full outer join (select * from cte where quarter='q2')  as b
                                 on a.rn=b.rn
    , cte2 as (select a.sales as s1
                             ,b.sales as s2
                             ,coalesce(a.rn,b.rn) as rn
                             from (select * from cte where quarter='q3') as a
                               full outer join (select * from cte where quarter='q4')  as b
                                 on a.rn=b.rn
     select a.s1 as q1,a.s2 as q2,b.s1 as q3,b.s2 as q4
      from cte1 as a full outer join cte2 as b
       on a.rn=b.rn
      order by a.rn,b.rn                    

    Author Closing Comment


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now