Is it Pivot? - 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 2005 Forums

Transact-SQL (2005)

Is it Pivot?

Author

Topic

mayaaa
Starting Member

15 Posts

Posted - 2009-03-21 : 15:44:24

Hey there,
Sql Server 2005.
I have a table with 5 fields: quarter,KPI_1,KPI_2,KPI_3,KPI_4.
the data display as follow:
Quarter KPI_1 KPI_2 KPI_3 KPI_4
Q1 300 234 237 667
Q1 587 455 988 786
Q2 123 556 912 432
Q2 378 77 889 675

I need the data to be displayed as follow:

KPI Q1 Q2
KPI_1 887 501
KPI_2 689 633
KPI_3 1225 1801
KPI_4 1453 1107

I've tried to use some pivot examples but it seems nothing works for me.
I would appreciate your help very much.
Thanks in advance :)

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-21 : 23:55:19

This is unpivot

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)

INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)

SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-22 : 00:27:58

[code]
DECLARE @tab TABLE(Quarter1 VARCHAR(12), KPI_1 INT,KPI_2 INT, KPI_3 INT, KPI_4 INT)
INSERT INTO @tab SELECT 'Q1', 300, 234, 237, 667 UNION ALL SELECT
'Q1', 587, 455, 988, 786 UNION ALL SELECT
'Q2', 123, 556, 912, 432 UNION ALL SELECT
'Q2', 378,77, 889, 675

SELECT Quarter1,kpi,kpivalue into #temp
FROM
@tab t
UNPIVOT
(kpivalue FOR kpi IN(KPI_1 ,KPI_2 , KPI_3 , KPI_4)) as unpvt

SELECT kpi,[q1],[q2]
FROM #temp t
PIVOT(sum(kpivalue) FOR quarter1 IN([q1],[q2]))pvt

drop table #temp
[/code]
check this is this u want

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-22 : 00:30:02

[code]
SELECT Quarter1,kpi,kpivalue into #temp
FROM
@tab t
UNPIVOT
(kpivalue FOR kpi IN(KPI_1 ,KPI_2 , KPI_3 , KPI_4)) as unpvt

SELECT kpi, SUM(CASE WHEN quarter1 = 'Q1' THEN kpivalue END )as q1,
SUM(CASE WHEN quarter1 = 'Q2' THEN kpivalue END )as q2
FROM #temp
GROUP BY kpi

drop table #temp
[/code]

mayaaa
Starting Member

15 Posts

Posted - 2009-03-22 : 05:04:39

Thank you very much Nageswar9 and bklr :)
It was very helpfull.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-22 : 07:52:53

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources