SQLTeam.com | Weblogs | Forums

Format of result, head category subcategory'


#1

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)
The idea is to create a CSS menu from the results.
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

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

everytime the head category changes it has to insert
Can use a Cursor, since i will be storing the result. (and then cached in the application)

thanks a lot


#2

I'm not sure what output you want.


#3

Hi

Please find my solution below
and please let me know if its correct

Data Create Script

DROP TABLE #temptable

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' );

My Solution

;WITH abc_cte
AS (SELECT Row_number()
OVER(
partition BY categoryid
ORDER BY categoryid) AS rownum,
categoryid,
subcategoryid,
lang
FROM #temptable)
SELECT *
INTO #temptable2
FROM abc_cte
WHERE rownum = 1

SELECT *
FROM #temptable2


#4

i wasnt clear (as usual ;-))
it simple.
i want the head category followed be the sub categories

headcat
subcat
subcat
subcat
subcat
headcat
subcat
subcat
subcat
headcat


#5

Hi

Is this the solution you are looking for ?
Please let me know

Query

;WITH abc_cte
AS (SELECT DISTINCT categoryid,
NULL AS x
FROM #temptable
UNION
SELECT categoryid,
subcategoryid
FROM #temptable)
SELECT CASE
WHEN x IS NULL THEN categoryid
ELSE x
END
FROM abc_cte

Create Data Script

DROP TABLE #temptable

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' );


#6

Thanks a lot harishgg1, that worked perfectly