Need the Recordset with high performance

  • 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

  • -- 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

  • 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

  • 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