January 30, 2018 at 3:40 pm
Hi all, im trying to do something, but have no idea how to acief this.
Here part OF the DATA i'm with
A select returns all the row next to each other( logic) Its a nemu structure 7 languages (only 2 here=CREATE TABLE #temptable ( [CategoryID] int, [SubCategoryID] int, [Lang] nvarchar(2) )
INSERT INTO #temptable
VALUES
( 14, 1024, N'de' ),
( 14, 1025, N'de' ),
( 14, 1075, N'de' ),
( 14, 1106, N'de' ),
( 14, 1127, N'de' ),
( 16, 1042, N'de' ),
( 16, 1048, N'de' ),
( 16, 1113, N'de' ),
( 16, 1125, N'de' ),
( 17, 1039, N'de' ),
( 17, 1040, N'de' ),
( 17, 1049, N'de' ),
( 19, 1056, N'de' ),
( 19, 1057, N'de' ),
( 19, 1088, N'de' ),
( 19, 1119, N'de' ),
( 26, 1027, N'de' ),
( 26, 1029, N'de' ),
( 26, 1036, N'de' ),
( 26, 1037, N'de' ),
( 26, 1063, N'de' ),
( 26, 1107, N'de' ),
( 26, 1129, N'de' ),
( 29, 1010, N'de' ),
( 29, 1011, N'de' ),
( 29, 1045, N'de' ),
( 29, 1098, N'de' ),
( 29, 1109, N'de' ),
( 29, 1115, N'de' ),
( 29, 1116, N'de' ),
( 29, 1128, N'de' ),
( 36, 1055, N'de' ),
( 38, 1105, N'de' ),
( 41, 1081, N'de' ),
( 41, 1082, N'de' ),
( 41, 1083, N'de' ),
( 41, 1099, N'de' ),
( 43, 1090, N'de' ),
( 43, 1091, N'de' ),
( 43, 1108, N'de' ),
( 43, 1111, N'de' ),
( 43, 1136, N'de' ),
( 45, 1101, N'de' ),
( 45, 1102, N'de' ),
( 45, 1103, N'de' ),
( 45, 1130, N'de' ),
( 46, 1112, N'de' ),
( 47, 1121, N'de' ),
( 47, 1122, N'de' ),
( 47, 1123, N'de' ),
( 47, 1124, N'de' ),
( 47, 1131, N'de' ),
( 47, 1135, N'de' ),
( 48, 1021, N'de' ),
( 48, 1132, N'de' ),
( 48, 1133, N'de' ),
( 48, 1134, N'de' ),
( 49, 1008, N'de' ),
( 49, 1009, N'de' ),
( 49, 1018, N'de' ),
( 49, 1020, N'de' ),
( 49, 1054, N'de' ),
( 49, 1071, N'de' ),
( 49, 1084, N'de' ),
( 49, 1085, N'de' ),
( 14, 1024, N'en' ),
( 14, 1025, N'en' ),
( 14, 1075, N'en' ),
( 14, 1106, N'en' ),
( 14, 1127, N'en' ),
( 16, 1042, N'en' ),
( 16, 1048, N'en' ),
( 16, 1113, N'en' ),
( 16, 1125, N'en' ),
( 17, 1039, N'en' ),
( 17, 1040, N'en' ),
( 17, 1049, N'en' ),
( 19, 1056, N'en' ),
( 19, 1057, N'en' ),
( 19, 1088, N'en' ),
( 19, 1119, N'en' ),
( 26, 1027, N'en' ),
( 26, 1029, N'en' ),
( 26, 1036, N'en' ),
( 26, 1037, N'en' ),
( 26, 1063, N'en' ),
( 26, 1107, N'en' ),
( 26, 1129, N'en' ),
( 29, 1010, N'en' ),
( 29, 1011, N'en' ),
( 29, 1045, N'en' ),
( 29, 1098, N'en' ),
( 29, 1109, N'en' ),
( 29, 1115, N'en' ),
( 29, 1116, N'en' ),
( 29, 1128, N'en' ),
( 36, 1055, N'en' ),
( 38, 1105, N'en' ),
( 41, 1081, N'en' ),
( 41, 1082, N'en' ),
( 41, 1083, N'en' ),
( 41, 1099, N'en' ),
( 43, 1090, N'en' ),
( 43, 1091, N'en' ),
( 43, 1108, N'en' ),
( 43, 1111, N'en' ),
( 43, 1136, N'en' ),
( 45, 1101, N'en' ),
( 45, 1102, N'en' ),
( 45, 1103, N'en' ),
( 45, 1130, N'en' ),
( 46, 1112, N'en' ),
( 47, 1121, N'en' ),
( 47, 1122, N'en' ),
( 47, 1123, N'en' ),
( 47, 1124, N'en' ),
( 47, 1131, N'en' ),
( 47, 1135, N'en' ),
( 48, 1021, N'en' ),
( 48, 1132, N'en' ),
( 48, 1133, N'en' ),
( 48, 1134, N'en' ),
( 49, 1008, N'en' ),
( 49, 1009, N'en' ),
( 49, 1018, N'en' ),
( 49, 1020, N'en' ),
( 49, 1054, N'en' ),
( 49, 1071, N'en' ),
( 49, 1084, N'en' ),
( 49, 1085, N'en' )
--DROP TABLE #temptable
i want to display indentedCategoryID SubCategoryID Lang
i want to display indented
CategoryID SubCategoryID Lang
14
1024
1025
1127
1075
1106
16
1042
1048
1113
1125
17
1039
1040
1049
19
1056
1057
1088
1119
26
1027
1029
1036
1037
1063
1107
1129
29
1010
1011
1045
1098
1109
1115
1116
29
1128
36
1055
38
1105
41
1081
1082
1083
1099
1090
43
1091
43
1108
1111
1136
45
1101
1102
1103
1130
46
1112
Any Help would be great
February 1, 2018 at 7:43 am
mikeaspnet - Tuesday, January 30, 2018 3:40 PMHi all, im trying to do something, but have no idea how to acief this.Here part OF the DATA i'm with
A select returns all the row next to each other( logic) Its a nemu structure 7 languages (only 2 here=CREATE TABLE #temptable ( [CategoryID] int, [SubCategoryID] int, [Lang] nvarchar(2) )
INSERT INTO #temptable
VALUES
( 14, 1024, N'de' ),
( 14, 1025, N'de' ),
( 14, 1075, N'de' ),
( 14, 1106, N'de' ),
( 14, 1127, N'de' ),
( 16, 1042, N'de' ),
( 16, 1048, N'de' ),
( 16, 1113, N'de' ),
( 16, 1125, N'de' ),
( 17, 1039, N'de' ),
( 17, 1040, N'de' ),
( 17, 1049, N'de' ),
( 19, 1056, N'de' ),
( 19, 1057, N'de' ),
( 19, 1088, N'de' ),
( 19, 1119, N'de' ),
( 26, 1027, N'de' ),
( 26, 1029, N'de' ),
( 26, 1036, N'de' ),
( 26, 1037, N'de' ),
( 26, 1063, N'de' ),
( 26, 1107, N'de' ),
( 26, 1129, N'de' ),
( 29, 1010, N'de' ),
( 29, 1011, N'de' ),
( 29, 1045, N'de' ),
( 29, 1098, N'de' ),
( 29, 1109, N'de' ),
( 29, 1115, N'de' ),
( 29, 1116, N'de' ),
( 29, 1128, N'de' ),
( 36, 1055, N'de' ),
( 38, 1105, N'de' ),
( 41, 1081, N'de' ),
( 41, 1082, N'de' ),
( 41, 1083, N'de' ),
( 41, 1099, N'de' ),
( 43, 1090, N'de' ),
( 43, 1091, N'de' ),
( 43, 1108, N'de' ),
( 43, 1111, N'de' ),
( 43, 1136, N'de' ),
( 45, 1101, N'de' ),
( 45, 1102, N'de' ),
( 45, 1103, N'de' ),
( 45, 1130, N'de' ),
( 46, 1112, N'de' ),
( 47, 1121, N'de' ),
( 47, 1122, N'de' ),
( 47, 1123, N'de' ),
( 47, 1124, N'de' ),
( 47, 1131, N'de' ),
( 47, 1135, N'de' ),
( 48, 1021, N'de' ),
( 48, 1132, N'de' ),
( 48, 1133, N'de' ),
( 48, 1134, N'de' ),
( 49, 1008, N'de' ),
( 49, 1009, N'de' ),
( 49, 1018, N'de' ),
( 49, 1020, N'de' ),
( 49, 1054, N'de' ),
( 49, 1071, N'de' ),
( 49, 1084, N'de' ),
( 49, 1085, N'de' ),
( 14, 1024, N'en' ),
( 14, 1025, N'en' ),
( 14, 1075, N'en' ),
( 14, 1106, N'en' ),
( 14, 1127, N'en' ),
( 16, 1042, N'en' ),
( 16, 1048, N'en' ),
( 16, 1113, N'en' ),
( 16, 1125, N'en' ),
( 17, 1039, N'en' ),
( 17, 1040, N'en' ),
( 17, 1049, N'en' ),
( 19, 1056, N'en' ),
( 19, 1057, N'en' ),
( 19, 1088, N'en' ),
( 19, 1119, N'en' ),
( 26, 1027, N'en' ),
( 26, 1029, N'en' ),
( 26, 1036, N'en' ),
( 26, 1037, N'en' ),
( 26, 1063, N'en' ),
( 26, 1107, N'en' ),
( 26, 1129, N'en' ),
( 29, 1010, N'en' ),
( 29, 1011, N'en' ),
( 29, 1045, N'en' ),
( 29, 1098, N'en' ),
( 29, 1109, N'en' ),
( 29, 1115, N'en' ),
( 29, 1116, N'en' ),
( 29, 1128, N'en' ),
( 36, 1055, N'en' ),
( 38, 1105, N'en' ),
( 41, 1081, N'en' ),
( 41, 1082, N'en' ),
( 41, 1083, N'en' ),
( 41, 1099, N'en' ),
( 43, 1090, N'en' ),
( 43, 1091, N'en' ),
( 43, 1108, N'en' ),
( 43, 1111, N'en' ),
( 43, 1136, N'en' ),
( 45, 1101, N'en' ),
( 45, 1102, N'en' ),
( 45, 1103, N'en' ),
( 45, 1130, N'en' ),
( 46, 1112, N'en' ),
( 47, 1121, N'en' ),
( 47, 1122, N'en' ),
( 47, 1123, N'en' ),
( 47, 1124, N'en' ),
( 47, 1131, N'en' ),
( 47, 1135, N'en' ),
( 48, 1021, N'en' ),
( 48, 1132, N'en' ),
( 48, 1133, N'en' ),
( 48, 1134, N'en' ),
( 49, 1008, N'en' ),
( 49, 1009, N'en' ),
( 49, 1018, N'en' ),
( 49, 1020, N'en' ),
( 49, 1054, N'en' ),
( 49, 1071, N'en' ),
( 49, 1084, N'en' ),
( 49, 1085, N'en' )
--DROP TABLE #temptablei want to display indented
CategoryID SubCategoryID Lang
i want to display indented
CategoryID SubCategoryID Lang
14
1024
1025
1127
1075
1106
16
1042
1048
1113
1125
17
1039
1040
1049
19
1056
1057
1088
1119
26
1027
1029
1036
1037
1063
1107
1129
29
1010
1011
1045
1098
1109
1115
1116
29
1128
36
1055
38
1105
41
1081
1082
1083
1099
1090
43
1091
43
1108
1111
1136
45
1101
1102
1103
1130
46
1112Any Help would be great
This is a presentation issue and should be handled in your presentation layer. For instance, this is trivial in SSRS. What are you using to display your results?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 1, 2018 at 11:49 am
i want clear.
it simple.
i want the head category followed be the sub categories
headcat
subcat
subcat
subcat
subcat
headcat
subcat
subcat
subcat
headcat
February 1, 2018 at 11:57 am
mikeaspnet - Thursday, February 1, 2018 11:49 AMi want clear.
it simple.
i want the head category followed be the sub categoriesheadcat
subcat
subcat
subcat
subcat
headcat
subcat
subcat
subcat
headcat
Yes, it's simple. This is a presentation issue and should be handled in the presentation layer. Restating the same issue in a different way isn't going to change the underlying facts.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 1, 2018 at 12:53 pm
That was not my question.
i'm asking how can i achieve this in T-sql 🙂
Reason i want to do this in SQL because these are very heavy queries and want to cache the results.
I in DB field, so can feed fast and consistent presorted data to the web servers, so each server can do what it is best at.
And as far as i know SQL is the place to sort data and not the webserver.
February 1, 2018 at 2:34 pm
mikeaspnet - Thursday, February 1, 2018 12:53 PMThat was not my question.
i'm asking how can i achieve this in T-sql 🙂Reason i want to do this in SQL because these are very heavy queries and want to cache the results.
I in DB field, so can feed fast and consistent presorted data to the web servers, so each server can do what it is best at.
And as far as i know SQL is the place to sort data and not the webserver.
SQL tables are UNORDERED. Storing the data in tables in this format will not guarantee consistent presorted data. Furthermore, storing data in this format will prevent you from returning consistent presorted data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply