SQL Server Performance

SQL Problem

Discussion in 'ALL SQL SERVER QUESTIONS' started by Jeff Goulding, Nov 5, 2014.

  1. Jeff Goulding New Member

    I attached a problem question I have for revision. How do I write a script for this anyone? I can't seem to make the company name column have no repeating rows without deleting the other rows. I am so confused, can someone please help!?

    Attached Files:

  2. moh_hassan20 New Member

    You can use the ranking function ROW_NUMBER with partition and order by to build the script.
    The following example i use the common table expression with case .. end based on rn (alias for row_number) to display the TerritoryName only once and exclude the repeated value
    you can use multi ROW_NUMBER for the different columns ref ,Company_Name, total_calls in the same select and control case .. end.

    Code:
    USE AdventureWorks2012;
    GO
    ;with CTE (TerritoryName,FirstName,LastName,sal , rn)
    as
    (
    SELECT TerritoryName,
    FirstName, LastName,  SalesYTD sal ,
    ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD ) AS rn
    FROM Sales.vSalesPerson
    WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0
    )
    select
    CASE WHEN rn = 1 THEN TerritoryName  ELSE '' END  TerritoryName,
    FirstName,LastName,sal
    from CTE
    
    result of query:
    [IMG]

    Note in the select how "case when ... end" to supress the repeated values:
    CASE WHEN rn = 1 THEN TerritoryName ELSE '' END TerritoryName,
    you can find more examples in row_number function in my blog:
    http://mohhassan20.wordpress.com/2014/11/10/the-ranking-function-row_number-and-its-usage/

    let me know if you find any problem, or send me sample of text data , bz you attached image.

Share This Page