

Grasshopper
Group: General Forum Members
Last Login: Friday, May 10, 2013 5:41 AM
Points: 12,
Visits: 32


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 HumanResourse Finance HelpDesk ITDevision GrandTotal  1 AAACompanyLtd 122 83 63 563 831 2 XYZprivate Ltd 12 31 42  85 3 TeamOutsouce  21  11 32  GrandTotal 134 135 105 574 948 
Thanks and Regards, Lokesh GB




SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 8,289,
Visits: 17,981


 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
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.”  Gail Shaw}
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 Exploring Recursive CTEs by Example Dwain Camps




SSC Eights!
Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 863,
Visits: 1,307


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




Grasshopper
Group: General Forum Members
Last Login: Friday, May 10, 2013 5:41 AM
Points: 12,
Visits: 32


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



