• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

pls provide the query for given below in sqlserver2005


Hi
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
Regards
Ravi
0
mu_ravi1
Asked:
mu_ravi1
3 Solutions
 
Imran Javed ZiaCommented:
Hi,
Please use following:

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


for other details please follow:
http://msdn.microsoft.com/en-us/library/ms177410(v=SQL.90).aspx
http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

Thanks
0
 
Pratima PharandeCommented:
SELECT q1,q2,q3,q4
FROM
(SELECT SomeName,sales
FROM #SomeTable) AS pivTemp
PIVOT
(   sales
    FOR SomeName IN (q1,q2,q3,q4)
) AS pivTable
0
 
Alpesh PatelAssistant ConsultantCommented:
Hi Use Pivot TAble to achieve solution.
0
 
LowfatspreadCommented:
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                    
0
 
mu_ravi1Author Commented:
good
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now