CODEWITHSUNDEEP
t-sqlgroup-byssrs-2008ssrs-groupingrow-number
Goran Obradovic
Goran Obradovic

Reputation: 9051

Add row number to outer group in t-sql or ssrs

I have a query that returns data with group category and some details like this:

Category | Title
==================
cat1  ---  titlex
cat1  ---  titley
cat2  ---  titley
cat3  ---  titlez
cat3  ---  titlex
cat4  ---  titlex

I want to display a table that has row number on outer group (Category) like this:

RN | Category | Title
======================
1    cat1  
                titlex
                titley
2    cat2
                titley
3    cat3
                titlez
                titlex
4    cat4 
                titlex

The problem is, when I add RN column as ROW_NUMBER in sql query or ROWNUMBER SSRS function (tried NOTHING, Group and Details as a scope, just in case), I always get numbers like 2 1 2 or 1 3 4 6 for RN column.

EDIT Sql Query (table names and properties changed for simplicity)

SELECT    
        -- this rownumber does not work, counts every occurrence of category    
    --ROW_NUMBER() OVER (
        --PARTITION BY c.Name -- tried this too, this resets on each cat
        --ORDER BY c.Name) AS RN,
    c.Name, 
    p.Name
FROM
    Products p INNER JOIN
    Categories c ON p.CategoryId = c.Id
GROUP BY c.Name, p.Name
ORDER BY c.Name, p.Name

Upvotes: 1

Views: 2727

Answers (2)

Jamie F
Jamie F

Reputation: 23789

You can also accomplish this at the presentation layer in SSRS:

=RunningValue(Fields!CategoryFieldName.Value, CountDistinct, Nothing)

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

You don't want the row numbers (as you've observed, the row numbers are assigned to every... um... row).

Maybe you want DENSE_RANK?

SELECT    
    DENSE_RANK() OVER (ORDER BY c.Name) AS RN,
    c.Name, 
    p.Name
FROM
    Products p INNER JOIN
    Categories c ON p.CategoryId = c.Id
GROUP BY c.Name, p.Name
ORDER BY c.Name, p.Name

As to your desired output, I wouldn't attempt to achieve that in SQL - use a reporting/formatting tool to get the final layout.

Upvotes: 3

Related Questions