database - How to group a couple of rows in SQL Server? -
i have query:
select table1.id, table1.code1, table1.code2, table1.details, table1.ids, table2.name table1 inner join table2 on table1.code1 = table2.code1 table1.ids = 1 order table1.code1, table1.code2 this result 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 want 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 result? please me. lot
if can rely on id column ordering groups (or combination of other rows, code1,code2) can in few different ways.
if server 2012+ can use lag() window function access previous rows , if previous rows code1 same current rows code1 replace null (or empty string if suits better). however, if you're using version < 2012 can accomplish using self join.
this kind of formatting might better handle on client side (or reporting layer) though if can.
the query below includes both versions, commented out self-join stuff:
select table1.id, -- case when table1.code1 = t1.code1 null else table1.code1 end code1, case when lag(table1.code1) on (order table1.id) = table1.code1 null else table1.code1 end code1, table1.code2, table1.details, table1.ids, -- case when table1.name = t1.name null else table1.name end name, case when lag(table2.name) on (order table1.id) = table2.name null else table2.name end name table1 inner join table2 on table1.code1 = table2.code1 -- left join table1 t1 on table1.id = t1.id + 1 table1.ids = 1 order table1.code1, table1.code2
Comments
Post a Comment