April 8, 2014 at 11:00 am
Table:
CREATE TABLE [dbo].[KPI](
[SVP] [varchar](20) NULL,
[Wk1] [int] NULL,
[Wk2] [int] NULL,
[Wk3] [int] NULL,
[Wk4] [int] NULL,
[Wk5] [int] NULL,
[Y] [int] NULL,
[int] NULL,
[Wk] [int] NULL
)
To generate sample data:
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 1,0,0,0,0,2014,1,1)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,2,0,0,0,2014,1,2)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,3,0,0,2014,1,3)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,4,0,2014,1,4)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,0,0,2014,1,5)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 30,0,0,0,0,2014,2,1)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,1,0,0,0,2014,2,2)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,2,0,0,2014,2,3)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,6,0,2014,2,4)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,0,9,2014,2,5)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 10,0,0,0,0,2014,3,1)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,32,0,0,0,2014,3,2)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,2,0,0,2014,3,3)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,6,0,2014,3,4)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,0,8,2014,3,5)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 17,0,0,0,0,2014,4,1)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,23,0,0,0,2014,4,2)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,31,0,0,2014,4,3)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,41,0,2014,4,4)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,0,1,2014,4,5)
Current result:
SVPWk1Wk2Wk3Wk4Wk5YQWk
SVP10000201411
SVP02000201412
SVP00300201413
SVP00040201414
SVP00000201415
SVP300000201421
SVP01000201422
SVP00200201423
SVP00060201424
SVP00009201425
SVP100000201431
SVP032000201432
SVP00200201433
SVP00060201434
SVP00008201435
SVP170000201441
SVP023000201442
SVP003100201443
SVP000410201444
SVP00001201445
Expected result:
SVPWk1Wk2Wk3Wk4Wk5YQ
SVP1234020141
SVP30 1 2 6 9 20142
SVP103226820143
SVP17233141120144
I surely can loop each row and insert the needed value into the result, I want to know if there is a better way to generate the result, thank you.
April 8, 2014 at 11:15 am
this will work for your sample data.....but maybe there are other issues as well?
SELECT SVP,
SUM(Wk1) AS wk1,
SUM(Wk2) AS wk2,
SUM(Wk3) AS wk3,
SUM(Wk4) AS wk4,
SUM(Wk5) AS wk5,
Y,
Q
FROM KPI
GROUP BY SVP,Y,Q
ORDER BY SVP,Y,Q
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 8, 2014 at 12:56 pm
That was quick and simple, thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply