Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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  
 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