May 9, 2013 at 8:52 am
DECLARE @TblData TABLE( OrganizationName VARCHAR(100), DeptName VARCHAR(50), Total int)
INSERT INTO @TblData VALUES( 'AAACompanyLtd', 'HumanResourse', 122)
INSERT INTO @TblData VALUES( 'AAACompanyLtd', 'Finance', 83)
INSERT INTO @TblData VALUES( 'AAACompanyLtd', 'HelpDesk', 63)
INSERT INTO @TblData VALUES( 'AAACompanyLtd', 'ITDevision', 563)
INSERT INTO @TblData VALUES( 'XYZprivateLtd', 'HumanResourse', 12)
INSERT INTO @TblData VALUES( 'XYZprivateLtd', 'Finance', 31)
INSERT INTO @TblData VALUES( 'XYZprivateLtd', 'HelpDesk', 42)
INSERT INTO @TblData VALUES( 'TeamOutsouce', 'Finance', 21)
INSERT INTO @TblData VALUES( 'TeamOutsouce', 'ITDevision', 11)
select * from @TblData
I need the record set in MS SQL as below. Please help. I am using MS SQL server 2008
--------------------------------------------------------------------------------------
SlNo#OrganizationName HumanResourseFinanceHelpDeskITDevisionGrandTotal
--------------------------------------------------------------------------------------
1AAACompanyLtd1228363563831
2XYZprivate Ltd123142-85
3TeamOutsouce-21-1132
-------------------------------------------------------------------------------------
GrandTotal 134135105574948
-------------------------------------------------------------------------------------
Thanks and Regards,
Lokesh GB
May 9, 2013 at 9:04 am
-- standard crosstab query
SELECT [SlNo#] = ROW_NUMBER() OVER(ORDER BY GrandTotal DESC),
OrganizationName, HumanResourse, Finance, HelpDesk, ITDevision, GrandTotal
FROM (
SELECT
OrganizationName,
HumanResourse= SUM(CASE WHEN DeptName = 'HumanResourse' THEN Total ELSE 0 END),
Finance= SUM(CASE WHEN DeptName = 'Finance' THEN Total ELSE 0 END),
HelpDesk= SUM(CASE WHEN DeptName = 'HelpDesk' THEN Total ELSE 0 END),
ITDevision= SUM(CASE WHEN DeptName = 'ITDevision' THEN Total ELSE 0 END),
GrandTotal= SUM(Total)
FROM @TblData
GROUP BY OrganizationName
) d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 9, 2013 at 9:37 am
He can use roll up to get the bottom total too like below but I am not able to sort it right as he want based on Grandtotal.
SELECT [SlNo#] = ROW_NUMBER() OVER(ORDER BY OrganizationName DESC),
OrganizationName, HumanResourse, Finance, HelpDesk, ITDevision, GrandTotal
FROM (
SELECT top 100 percent
CASE WHEN (GROUPING(OrganizationName) = 1) THEN ' GrandTotal'
ELSE ISNULL(OrganizationName, 'UNKNOWN') end as OrganizationName ,
--OrganizationName,
HumanResourse= SUM(CASE WHEN DeptName = 'HumanResourse' THEN Total ELSE 0 END),
Finance= SUM(CASE WHEN DeptName = 'Finance' THEN Total ELSE 0 END),
HelpDesk= SUM(CASE WHEN DeptName = 'HelpDesk' THEN Total ELSE 0 END),
ITDevision= SUM(CASE WHEN DeptName = 'ITDevision' THEN Total ELSE 0 END),
GrandTotal= SUM(Total)
FROM TblData
GROUP BY OrganizationName WITH ROLLUP
--COMPUTE SUM(Total)
) d
May 9, 2013 at 12:33 pm
Thanks Guys It helped me lot.
Also I tried it to sortby Organizations since GrandTotal needs to appear on bottom
SELECT [SlNo#] = ROW_NUMBER() OVER(ORDER BY CASE WHEN ORGANIZATIONNAME = 'GrandTotal' THEN 'ZZZZZZ' ELSE ORGANIZATIONNAME END Asc),
ORGANIZATIONNAME, HUMANRESOURSE, FINANCE, HELPDESK, ITDEVISION, GRANDTOTAL
FROM (
SELECT top 100 percent
CASE WHEN (GROUPING(OrganizationName) = 1) THEN 'GrandTotal'
ELSE ISNULL(OrganizationName, 'UNKNOWN') end as OrganizationName ,
--OrganizationName,
HumanResourse= SUM(CASE WHEN DeptName = 'HumanResourse' THEN Total ELSE 0 END),
Finance= SUM(CASE WHEN DeptName = 'Finance' THEN Total ELSE 0 END),
HelpDesk= SUM(CASE WHEN DeptName = 'HelpDesk' THEN Total ELSE 0 END),
ITDevision= SUM(CASE WHEN DeptName = 'ITDevision' THEN Total ELSE 0 END),
GrandTotal= SUM(Total)
FROM @TblData
GROUP BY OrganizationName WITH ROLLUP
) d
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply