switch x and y axis - SQL Server Forums

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

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

switch x and y axis

Author

Topic

Mikehjun
Starting Member

24 Posts

Posted - 2013-03-27 : 11:26:40

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

54 Posts

Posted - 2013-03-27 : 19:52:07

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-28 : 04:55:01

[code]
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'
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources