Display format for repeating value in each column
up vote
0
down vote
favorite
I have a table structure of an organization having 3 columns:
- DEPARTMENT
- SECTION
- SUBSECTION
DEPARTMENT SECTION SUBSECTION
---------- -------- -----------
A A1 A101
A A1 A102
A A2 A201
A A2 A202
B B1 B101
B B1 B102
B B2 B201
B B2 B202
And I want to display in a sense that each column, it only display the first appearance of the duplicated value. Expected output as below,
DEPARTMENT SECTION SUBSECTION
---------- --------- -----------
A A1 A101
A102
A2 A201
A202
B B1 B101
B102
B2 B201
B202
sql
sql-server tsql sql-server-2008 edited Nov 12 at 15:57
Andrea
7,493144551
asked Nov 12 at 1:20
Eliseo Jr
5910
add a comment |
up vote
0
down vote
favorite
I have a table structure of an organization having 3 columns:
- DEPARTMENT
- SECTION
- SUBSECTION
DEPARTMENT SECTION SUBSECTION
---------- -------- -----------
A A1 A101
A A1 A102
A A2 A201
A A2 A202
B B1 B101
B B1 B102
B B2 B201
B B2 B202
And I want to display in a sense that each column, it only display the first appearance of the duplicated value. Expected output as below,
DEPARTMENT SECTION SUBSECTION
---------- --------- -----------
A A1 A101
A102
A2 A201
A202
B B1 B101
B102
B2 B201
B202
sql
sql-server tsql sql-server-2008 edited Nov 12 at 15:57
Andrea
7,493144551
asked Nov 12 at 1:20
Eliseo Jr
5910
2
This should be done in the presentation layer.
– PM 77-1
Nov 12 at 1:22
@PM77-1 , that was also my next step if it cannot be done in database layer.
– Eliseo Jr
Nov 12 at 1:40
1
It can be done with SQL but shouldn't.
– PM 77-1
Nov 12 at 1:47
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a table structure of an organization having 3 columns:
- DEPARTMENT
- SECTION
- SUBSECTION
DEPARTMENT SECTION SUBSECTION
---------- -------- -----------
A A1 A101
A A1 A102
A A2 A201
A A2 A202
B B1 B101
B B1 B102
B B2 B201
B B2 B202
And I want to display in a sense that each column, it only display the first appearance of the duplicated value. Expected output as below,
DEPARTMENT SECTION SUBSECTION
---------- --------- -----------
A A1 A101
A102
A2 A201
A202
B B1 B101
B102
B2 B201
B202
sql
sql-server tsql sql-server-2008 edited Nov 12 at 15:57
Andrea
7,493144551
asked Nov 12 at 1:20
Eliseo Jr
5910
I have a table structure of an organization having 3 columns:
- DEPARTMENT
- SECTION
- SUBSECTION
DEPARTMENT SECTION SUBSECTION
---------- -------- -----------
A A1 A101
A A1 A102
A A2 A201
A A2 A202
B B1 B101
B B1 B102
B B2 B201
B B2 B202
And I want to display in a sense that each column, it only display the first appearance of the duplicated value. Expected output as below,
DEPARTMENT SECTION SUBSECTION
---------- --------- -----------
A A1 A101
A102
A2 A201
A202
B B1 B101
B102
B2 B201
B202
sql
sql-server tsql sql-server-2008 sql
sql-server tsql sql-server-2008 edited Nov 12 at 15:57
Andrea
7,493144551
asked Nov 12 at 1:20
Eliseo Jr
5910
edited Nov 12 at 15:57
Andrea
7,493144551
asked Nov 12 at 1:20
Eliseo Jr
5910
edited Nov 12 at 15:57
Andrea
7,493144551
edited Nov 12 at 15:57
Andrea
7,493144551
edited Nov 12 at 15:57
Andrea
7,493144551
7,493144551
asked Nov 12 at 1:20
Eliseo Jr
5910
asked Nov 12 at 1:20
Eliseo Jr
5910
asked Nov 12 at 1:20
Eliseo Jr
5910
5910
2
This should be done in the presentation layer.
– PM 77-1
Nov 12 at 1:22
@PM77-1 , that was also my next step if it cannot be done in database layer.
– Eliseo Jr
Nov 12 at 1:40
1
It can be done with SQL but shouldn't.
– PM 77-1
Nov 12 at 1:47
add a comment |
2
This should be done in the presentation layer.
– PM 77-1
Nov 12 at 1:22
@PM77-1 , that was also my next step if it cannot be done in database layer.
– Eliseo Jr
Nov 12 at 1:40
1
It can be done with SQL but shouldn't.
– PM 77-1
Nov 12 at 1:47
2
2
This should be done in the presentation layer.
– PM 77-1
Nov 12 at 1:22
This should be done in the presentation layer.
– PM 77-1
Nov 12 at 1:22
@PM77-1 , that was also my next step if it cannot be done in database layer.
– Eliseo Jr
Nov 12 at 1:40
@PM77-1 , that was also my next step if it cannot be done in database layer.
– Eliseo Jr
Nov 12 at 1:40
1
1
It can be done with SQL but shouldn't.
– PM 77-1
Nov 12 at 1:47
It can be done with SQL but shouldn't.
– PM 77-1
Nov 12 at 1:47
add a comment |
1 Answer
1
active
oldest
votes
up vote
3
down vote
accepted
As said in the comments you should really do this in your application layer; however you can accomplish this with window functions:
declare @tmp table (DEPARTMENT varchar(1), SECTION varchar(2), SUBSECTION varchar(4))
insert into @tmp values
('A', 'A1', 'A101')
,('A', 'A1', 'A102')
,('A', 'A2', 'A201')
,('A', 'A2', 'A202')
,('B', 'B1', 'B101')
,('B', 'B1', 'B102')
,('B', 'B2', 'B201')
,('B', 'B2', 'B202')
select
case when rn_dep = 1 then DEPARTMENT else '' end as DEPARTMENT
, case when rn_sec = 1 then SECTION else '' end as SECTION
, SUBSECTION
from (
select
DEPARTMENT,SECTION, SUBSECTION
, ROW_NUMBER() OVER (partition by DEPARTMENT ORDER BY DEPARTMENT, SECTION, SUBSECTION) as rn_dep
, ROW_NUMBER() OVER (partition by DEPARTMENT, SECTION ORDER BY SECTION, SUBSECTION) as rn_sec
from @tmp
) T
Result:
answered Nov 12 at 15:55
Andrea
7,493144551
thank you for this will be more faster, I designed in application layer too.
– Eliseo Jr
Nov 13 at 3:23
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
down vote
accepted
As said in the comments you should really do this in your application layer; however you can accomplish this with window functions:
declare @tmp table (DEPARTMENT varchar(1), SECTION varchar(2), SUBSECTION varchar(4))
insert into @tmp values
('A', 'A1', 'A101')
,('A', 'A1', 'A102')
,('A', 'A2', 'A201')
,('A', 'A2', 'A202')
,('B', 'B1', 'B101')
,('B', 'B1', 'B102')
,('B', 'B2', 'B201')
,('B', 'B2', 'B202')
select
case when rn_dep = 1 then DEPARTMENT else '' end as DEPARTMENT
, case when rn_sec = 1 then SECTION else '' end as SECTION
, SUBSECTION
from (
select
DEPARTMENT,SECTION, SUBSECTION
, ROW_NUMBER() OVER (partition by DEPARTMENT ORDER BY DEPARTMENT, SECTION, SUBSECTION) as rn_dep
, ROW_NUMBER() OVER (partition by DEPARTMENT, SECTION ORDER BY SECTION, SUBSECTION) as rn_sec
from @tmp
) T
Result:
answered Nov 12 at 15:55
Andrea
7,493144551
thank you for this will be more faster, I designed in application layer too.
– Eliseo Jr
Nov 13 at 3:23
add a comment |
up vote
3
down vote
accepted
As said in the comments you should really do this in your application layer; however you can accomplish this with window functions:
declare @tmp table (DEPARTMENT varchar(1), SECTION varchar(2), SUBSECTION varchar(4))
insert into @tmp values
('A', 'A1', 'A101')
,('A', 'A1', 'A102')
,('A', 'A2', 'A201')
,('A', 'A2', 'A202')
,('B', 'B1', 'B101')
,('B', 'B1', 'B102')
,('B', 'B2', 'B201')
,('B', 'B2', 'B202')
select
case when rn_dep = 1 then DEPARTMENT else '' end as DEPARTMENT
, case when rn_sec = 1 then SECTION else '' end as SECTION
, SUBSECTION
from (
select
DEPARTMENT,SECTION, SUBSECTION
, ROW_NUMBER() OVER (partition by DEPARTMENT ORDER BY DEPARTMENT, SECTION, SUBSECTION) as rn_dep
, ROW_NUMBER() OVER (partition by DEPARTMENT, SECTION ORDER BY SECTION, SUBSECTION) as rn_sec
from @tmp
) T
Result:
answered Nov 12 at 15:55
Andrea
7,493144551
thank you for this will be more faster, I designed in application layer too.
– Eliseo Jr
Nov 13 at 3:23
add a comment |
up vote
3
down vote
accepted
up vote
3
down vote
accepted
As said in the comments you should really do this in your application layer; however you can accomplish this with window functions:
declare @tmp table (DEPARTMENT varchar(1), SECTION varchar(2), SUBSECTION varchar(4))
insert into @tmp values
('A', 'A1', 'A101')
,('A', 'A1', 'A102')
,('A', 'A2', 'A201')
,('A', 'A2', 'A202')
,('B', 'B1', 'B101')
,('B', 'B1', 'B102')
,('B', 'B2', 'B201')
,('B', 'B2', 'B202')
select
case when rn_dep = 1 then DEPARTMENT else '' end as DEPARTMENT
, case when rn_sec = 1 then SECTION else '' end as SECTION
, SUBSECTION
from (
select
DEPARTMENT,SECTION, SUBSECTION
, ROW_NUMBER() OVER (partition by DEPARTMENT ORDER BY DEPARTMENT, SECTION, SUBSECTION) as rn_dep
, ROW_NUMBER() OVER (partition by DEPARTMENT, SECTION ORDER BY SECTION, SUBSECTION) as rn_sec
from @tmp
) T
Result:
answered Nov 12 at 15:55
Andrea
7,493144551
As said in the comments you should really do this in your application layer; however you can accomplish this with window functions:
declare @tmp table (DEPARTMENT varchar(1), SECTION varchar(2), SUBSECTION varchar(4))
insert into @tmp values
('A', 'A1', 'A101')
,('A', 'A1', 'A102')
,('A', 'A2', 'A201')
,('A', 'A2', 'A202')
,('B', 'B1', 'B101')
,('B', 'B1', 'B102')
,('B', 'B2', 'B201')
,('B', 'B2', 'B202')
select
case when rn_dep = 1 then DEPARTMENT else '' end as DEPARTMENT
, case when rn_sec = 1 then SECTION else '' end as SECTION
, SUBSECTION
from (
select
DEPARTMENT,SECTION, SUBSECTION
, ROW_NUMBER() OVER (partition by DEPARTMENT ORDER BY DEPARTMENT, SECTION, SUBSECTION) as rn_dep
, ROW_NUMBER() OVER (partition by DEPARTMENT, SECTION ORDER BY SECTION, SUBSECTION) as rn_sec
from @tmp
) T
Result:
answered Nov 12 at 15:55
Andrea
7,493144551
answered Nov 12 at 15:55
Andrea
7,493144551
answered Nov 12 at 15:55
Andrea
7,493144551
answered Nov 12 at 15:55
Andrea
7,493144551
7,493144551
thank you for this will be more faster, I designed in application layer too.
– Eliseo Jr
Nov 13 at 3:23
add a comment |
thank you for this will be more faster, I designed in application layer too.
– Eliseo Jr
Nov 13 at 3:23
thank you for this will be more faster, I designed in application layer too.
– Eliseo Jr
Nov 13 at 3:23
thank you for this will be more faster, I designed in application layer too.
– Eliseo Jr
Nov 13 at 3:23
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53254884%2fdisplay-format-for-repeating-value-in-each-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
This should be done in the presentation layer.
– PM 77-1
Nov 12 at 1:22
@PM77-1 , that was also my next step if it cannot be done in database layer.
– Eliseo Jr
Nov 12 at 1:40
1
It can be done with SQL but shouldn't.
– PM 77-1
Nov 12 at 1:47