Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need the Recordset with high performance Expand / Collapse
Author
Message
Posted Thursday, May 9, 2013 8:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Post #1451170
Posted Thursday, May 9, 2013 9:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 7,133, Visits: 13,518
-- 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
Post #1451177
Posted Thursday, May 9, 2013 9:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:21 PM
Points: 809, Visits: 1,160
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

Post #1451203
Posted Thursday, May 9, 2013 12:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

Post #1451296
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse