Rows to Columns
-
Provided the following table return the result set so that for each employee the bonus amount is displayed by each month as column.
DECLARE @tblFun Table
(
sEmpId INT,
sMonth CHAR(3),
fBonus_amt float,
PRIMARY KEY (sEmpId,sMonth)
)
Result looks like (for display purpose I made it comma separated)
sEmpId,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC
10,500.0,50.0,100.0,40.0,600.0,80.0,1000.0,50.0,500.0,200.0,100.0,100.0
20,300.0,60.0,0.0,40.0,600.0,20.0,0.0,0.0,0.0,500.0,800.0,3400.0
I have the solution for this using case statement but for GOD knows reason somebody wanted me to implement it without CASE or UNION of 12 tables.
SO PLEASE SUGGEST ME A SOLUTION WHICH DOES NOT USE CASE STATEMENT OR UNION OF 12 TABLES.
Helping data set
INSERT INTO @tblFun VALUES ( 10, 'JAN', 500)
INSERT INTO @tblFun VALUES ( 10, 'FEB', 50)
INSERT INTO @tblFun VALUES ( 10, 'MAR', 100)
INSERT INTO @tblFun VALUES ( 10, 'APR', 40)
INSERT INTO @tblFun VALUES ( 10, 'MAY', 600)
INSERT INTO @tblFun VALUES ( 10, 'JUN', 80)
INSERT INTO @tblFun VALUES ( 10, 'JUL', 1000)
INSERT INTO @tblFun VALUES ( 10, 'AUG', 50)
INSERT INTO @tblFun VALUES ( 10, 'SEP', 500)
INSERT INTO @tblFun VALUES ( 10, 'OCT', 200)
INSERT INTO @tblFun VALUES ( 10, 'NOV', 100)
INSERT INTO @tblFun VALUES ( 10, 'DEC', 100)
INSERT INTO @tblFun VALUES ( 20, 'JAN', 300)
INSERT INTO @tblFun VALUES ( 20, 'FEB', 60)
INSERT INTO @tblFun VALUES ( 20, 'APR', 40)
INSERT INTO @tblFun VALUES ( 20, 'MAY', 600)
INSERT INTO @tblFun VALUES ( 20, 'JUN', 20)
INSERT INTO @tblFun VALUES ( 20, 'OCT', 500)
INSERT INTO @tblFun VALUES ( 20, 'NOV', 800)
INSERT INTO @tblFun VALUES ( 20, 'DEC', 3400)
For those just interested in the solution with CASE..
SELECT sEmpId
,SUM(CASE WHEN sMonth = 'JAN' THEN fbonus_amt ELSE 0 END) as 'JAN'
,SUM(CASE WHEN sMonth = 'FEB' THEN fbonus_amt ELSE 0 END) as 'FEB'
,SUM(CASE WHEN sMonth = 'MAR' THEN fbonus_amt ELSE 0 END) as 'MAR'
,SUM(CASE WHEN sMonth = 'APR' THEN fbonus_amt ELSE 0 END) as 'APR'
,SUM(CASE WHEN sMonth = 'MAY' THEN fbonus_amt ELSE 0 END) as 'MAY'
,SUM(CASE WHEN sMonth = 'JUN' THEN fbonus_amt ELSE 0 END) as 'JUN'
,SUM(CASE WHEN sMonth = 'JUL' THEN fbonus_amt ELSE 0 END) as 'JUL'
,SUM(CASE WHEN sMonth = 'AUG' THEN fbonus_amt ELSE 0 END) as 'AUG'
,SUM(CASE WHEN sMonth = 'SEP' THEN fbonus_amt ELSE 0 END) as 'SEP'
,SUM(CASE WHEN sMonth = 'OCT' THEN fbonus_amt ELSE 0 END) as 'OCT'
,SUM(CASE WHEN sMonth = 'NOV' THEN fbonus_amt ELSE 0 END) as 'NOV'
,SUM(CASE WHEN sMonth = 'DEC' THEN fbonus_amt ELSE 0 END) as 'DEC'
FROM @tblFUn
GROUP BY sEmpId
-
Who is this someone?
And does this someone know that the case statement is the most effective way to implement this?
What does this someone Propose you do instead?
You have the solution, and I would not recommend any other way.
-
Oh, come on! Maybe he's just curious how us old fogeys used to do things in the old days when computers were still coal powered and there were no such newfangled-namby-pamby "case" statements:
CREATE TABLE #tx
( sMonth CHAR(3)
,JanMult FLOAT
,FebMult FLOAT
,MarMult FLOAT
,AprMult FLOAT
,MayMult FLOAT
,JunMult FLOAT
,JulMult FLOAT
,AugMult FLOAT
,SepMult FLOAT
,OctMult FLOAT
,NovMult FLOAT
,DecMult Float )
INSERT INTO #tx SELECT 'JAN', 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
INSERT INTO #tx SELECT 'FEB', 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
INSERT INTO #tx SELECT 'MAR', 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0
INSERT INTO #tx SELECT 'APR', 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0
INSERT INTO #tx SELECT 'MAY', 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0
INSERT INTO #tx SELECT 'JUN', 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0
INSERT INTO #tx SELECT 'JUL', 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0
INSERT INTO #tx SELECT 'AUG', 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0
INSERT INTO #tx SELECT 'SEP', 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0
INSERT INTO #tx SELECT 'OCT', 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0
INSERT INTO #tx SELECT 'NOV', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0
INSERT INTO #tx SELECT 'DEC', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1
SELECT t.sEmpId
,t.fBonus_amt * JanMult AS JAN
,t.fBonus_amt * FEBMult AS FEB
,t.fBonus_amt * MARMult AS MAR
,t.fBonus_amt * APRMult AS APR
,t.fBonus_amt * MAYMult AS MAY
,t.fBonus_amt * JUNMult AS JUN
,t.fBonus_amt * JULMult AS JUL
,t.fBonus_amt * AUGMult AS AUG
,t.fBonus_amt * SEPMult AS SEP
,t.fBonus_amt * OCTMult AS OCT
,t.fBonus_amt * NOVMult AS NOV
,t.fBonus_amt * DECMult AS DEC
FROM #tblFun t
JOIN #tx x ON x.sMonth = t.sMonth
(I'm sure the "group by" does not need to be illustrated.)
Yes, CASE makes things simpler. Why would anyone not use CASE if they could?
(BTW: using "tbl" as a prefix for tables is in fact one of the stupidest conventions ever created for SQL... Right up there with hungarian notation for columns.)
-
Thanks for your response. The 'someone' is nothing but one of big finencial company SQL DBA on Sybase side. May sybase doesn't have CASE yet.
BTW. Here I have put together your suggestions and final solution. Though it is much slower than CASE.
SELECT sEmpId
, SUM(fBonus_amt * JanMulti) as Jan
, SUM(fBonus_amt * FebMulti) as Feb
, SUM(fBonus_amt * MarMulti) as Mar
, SUM(fBonus_amt * AprMulti) as Apr
, SUM(fBonus_amt * MayMulti) as May
, SUM(fBonus_amt * JunMulti) as Jun
, SUM(fBonus_amt * JulMulti) as Jul
, SUM(fBonus_amt * AugMulti) as Aug
, SUM(fBonus_amt * SepMulti) as Sep
, SUM(fBonus_amt * OctMulti) as Oct
, SUM(fBonus_amt * NovMulti) as Nov
, SUM(fBonus_amt * DecMulti) as Dec
FROM @tblFun T
JOIN
(SELECT 'JAN' sMonth , 1 JanMulti, 0 FebMulti, 0 MarMulti, 0 AprMulti, 0 MayMulti, 0 JunMulti, 0 JulMulti, 0 AugMulti, 0 SepMulti, 0 OctMulti, 0 NovMulti, 0 DecMulti
UNION ALL
SELECT 'FEB', 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
UNION ALL
SELECT 'MAR', 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0
UNION ALL
SELECT 'APR', 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0
UNION ALL
SELECT 'MAY', 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0
UNION ALL
SELECT 'JUN', 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0
UNION ALL
SELECT 'JUL', 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0
UNION ALL
SELECT 'AUG', 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0
UNION ALL
SELECT 'SEP', 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0
UNION ALL
SELECT 'OCT', 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0
UNION ALL
SELECT 'NOV', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0
UNION ALL
SELECT 'DEC', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1
) M ON T.sMonth = M.sMonth
GROUP BY sEmpId
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply