SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 switch x and y axis
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mikehjun
Starting Member

24 Posts

Posted - 03/27/2013 :  11:26:40  Show Profile  Reply with Quote
Sum of the total miles for each quarter show like this,
quarter total
1 123
2 345
3 23
4 300

however I want to get the result like this
1q 2q 3q 4q
123 345 23 300


Any idea?

my T-sql;

SELECT  DATEPART("Q", I.[ACTFINISHDATE]) AS QUARTER
	   ,SUM(S.[Length]) AS "SUBTOTAL"
FROM [CLGT].[INSPECTION] I
INNER JOIN [LGDM].[SSGRAVITYMAIN] S
ON I.ENTITYUID = S.FACILITYID
WHERE I.STATUS = 'CLOSED' AND DATEPART("YY", I.[ACTFINISHDATE]) = '2012'
GROUP BY DATEPART("Q", I.[ACTFINISHDATE])

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 03/27/2013 :  19:52:07  Show Profile  Reply with Quote
WITH PivotData AS
(
SELECT
< grouping column >,
< spreading column >,
< aggregation column >
FROM < source table >
)
SELECT < select list >
FROM PivotData
PIVOT( < aggregate function >(< aggregation column >)
FOR < spreading column > IN (< distinct spreading values >) ) AS P;

or


create table #test (ID int, Val int)
go
insert into #test
select 1, 123
insert into #test
select 2, 345
insert into #test
select 3, 23
insert into #test
select 4, 300

go
select [1] as [1Q], [2] as [2Q], [3] as [3Q],[4] as [4Q]
FROM #test
PIVOT(sum(val) FOR ID IN ([1],[2],[3],[4]) ) AS P;

-- Im sure you can figure out the rest
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/28/2013 :  04:55:01  Show Profile  Reply with Quote

SELECT  SUM(CASE WHEN DATEPART("Q", I.[ACTFINISHDATE]) = 1 THEN S.[Length] ELSE 0 END) AS [1Q],
SUM(CASE WHEN DATEPART("Q", I.[ACTFINISHDATE]) = 2 THEN S.[Length] ELSE 0 END) AS [2Q],
SUM(CASE WHEN DATEPART("Q", I.[ACTFINISHDATE]) = 3 THEN S.[Length] ELSE 0 END) AS [3Q],
SUM(CASE WHEN DATEPART("Q", I.[ACTFINISHDATE]) = 4 THEN S.[Length] ELSE 0 END) AS [4Q]
FROM [CLGT].[INSPECTION] I
INNER JOIN [LGDM].[SSGRAVITYMAIN] S
ON I.ENTITYUID = S.FACILITYID
WHERE I.STATUS = 'CLOSED' AND DATEPART("YY", I.[ACTFINISHDATE]) = '2012'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000