CODEWITHSUNDEEP
sql-serverdatabase
Morteza Karimi
Morteza Karimi

Reputation: 107

How to group a couple of rows in SQL Server?

I have this query:

SELECT 
    Table1.ID, Table1.Code1, Table1.Code2, Table1.Details, 
    Table1.IDS, Table2.Name 
FROM 
    Table1 
INNER JOIN 
    Table2 ON Table1.Code1 = Table2.Code1 
WHERE 
    Table1.IDS = 1 
ORDER BY 
    Table1.Code1, Table1.Code2

This is my result for query:

ID    Code1   Code2   Details   IDS    Name
1     1001     01       D1       1      N1
2     1001     01       D2       1      N1
3     1001     02       D3       1      N1
4     1001     05       D4       1      N1
5     1002     11       D5       1      N2
6     1002     12       D6       1      N2
7     1005     21       D7       1      N3
8     1005     21       D8       1      N3

But I want this result:

ID    Code1   Code2   Details   IDS    Name
1     1001     01       D1       1      N1
2              01       D2       1
3              02       D3       1
4              05       D4       1
5     1002     11       D5       1      N2
6              12       D6       1
7     1005     21       D7       1      N3
8              21       D8       1

How do I get this result? Please help me. Thanks a lot

Upvotes: 1

Views: 586

Answers (4)

FutbolFan
FutbolFan

Reputation: 13713

Using ROW_NUMBER() within CTE or a subquery, here is one way to get your expected output:

;WITH q1 as
(
SELECT 
    t1.ID, 
    t1.Code1, 
    t1.Code2,
    t1.Details, 
    t1.IDS, 
    t2.Name,
    ROW_NUMBER() OVER (PARTITION BY t1.Code1 ORDER BY t1.ID) as rn
FROM 
    table1 t1 
INNER JOIN 
    Table2 t2 ON t1.Code1 = t2.Code1 
)

SELECT 
    q1.ID, 
    CASE
      WHEN rn = 1 THEN q1.Code1
    ELSE ''
    END as Code1, --only populate first row for each code1
    q1.Code2,
    q1.Details, 
    q1.IDS, 
    CASE
      WHEN rn = 1 THEN q1.Name
    ELSE ''
    END as Name  --only populate first row for each name
FROM 
    q1
WHERE 
    q1.IDS = 1 
ORDER BY 
    q1.Code1, q1.Code2

SQL Fiddle Demo

Upvotes: 0

Jayakumaur
Jayakumaur

Reputation: 1

Morteza,

This is a clear case of a presentation/UI layer requirement. Databases are made for a particular purpose and that is to crunch data and present you with results. I'd highly recommend you to turn to the front end logic for achieving your purpose.

Upvotes: 0

reaanb
reaanb

Reputation: 10066

Embedding presentation logic in your query isn't ideal. I recommend you process the query results programmatically, either to detect when groups change as you iterate, or to transform the query results into a nested table. The latter can be generalized as a reusable function.

Upvotes: 1

jpw
jpw

Reputation: 44871

If you can rely on the ID column for ordering the groups (or a combination of other rows, like code1,code2) then you can do this in a few different ways.

If your server is 2012+ then you can use the LAG() window function to access previous rows and if the previous rows Code1 is the same as the current rows Code1 replace it with null (or an empty string if that suits you better). However, if you're using a version < 2012 then you can accomplish it using a self join.

This kind of formatting might be better to handle on the client side (or reporting layer) though if can.

The query below includes both versions, but I commented out the self-join stuff:

SELECT 
    Table1.ID,

    -- CASE WHEN Table1.Code1 = t1.Code1 THEN NULL ELSE Table1.Code1 END AS Code1,
    CASE WHEN LAG(Table1.Code1) OVER (ORDER BY Table1.ID) = Table1.Code1 THEN NULL ELSE Table1.Code1 END AS Code1,

    Table1.Code2, Table1.Details, Table1.IDS, 

    -- CASE WHEN Table1.Name = t1.Name THEN NULL ELSE Table1.Name END AS Name,
    CASE WHEN LAG(Table2.Name) OVER (ORDER BY Table1.ID) = Table2.Name THEN NULL ELSE Table2.Name END AS Name

FROM 
    Table1 
INNER JOIN 
    Table2 ON Table1.Code1 = Table2.Code1 
-- LEFT JOIN Table1 t1 ON Table1.ID = t1.ID + 1 
WHERE 
    Table1.IDS = 1 
ORDER BY 
    Table1.Code1, Table1.Code2

Sample SQL Fiddle

Upvotes: 0

Related Questions