June 19, 2018 at 8:38 am
Hello,
I am new to the tsql and json stuff, so i will try and explain the best i can, here is the example below:
CREATE TABLE #student
(
id INT,
name VARCHAR(56) NOT NULL,
classes varchar(56) null,
)
insert into #student(id,name,classes)
values(1,'bob','history')
insert into #student(id,name,classes)
values(1,'bob','math')
insert into #student(id,name,classes)
values(1,'bob','science')
insert into #student(id,name,classes)
values(2,'susan','history')
insert into #student(id,name,classes)
values(2,'susan','math')
select *
from #student
as you can see, the problem and solution i want and if possible, is to put the duplicate rows into 1 row, for the example above, bob, in 1 row, but with json have as 1 but with json key value classes:history,math,science, same with susan, but she would have only: classes:history,math... i am trying but for some reason, i can only have it show the first class, and not the other classes in a comma, but wondering if that is how json best practice?
thanks in advance
June 19, 2018 at 10:39 am
First, why are you trying to use JSON for this? JSON is expensive to parse, and constructing JSON is even more challenging. You may want to search the web for SQL to construct JSON, and see what you find. You can rather easily use FOR XML PATH('') to concatenate the values by student, but you're using an RDBMS to do such things? I'm much more interested in the WHY behind your question than the question itself. Post back with your objective in fairly detailed terms and let's see if we can't get you a better solution.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 19, 2018 at 10:48 am
Hello sgmunson,
thanks for getting back to me,
i am doing this to help eliminate duplicate rows when i join it back to another table, which the table is called classes, the classes table is just class information. but when i Left join it, sometimes it causes duplicates, because there was 1 other thing i forgot to mention, and sad to say... they way the table structure is... its joining the tables on varchar columns and the to foreign keys or both varchar to join the 2 tables together, the students and classes table, plus i also forgot to mention, there is a data dictionary table, so i the joining table would look like:
Student --> join to ---> data dictionary table --> join to --> classes
in order to join correctly... but i wanted to make my example simple, as the objective is to get the student table into possibly a json format that can hopefully solve some duplicate issue.
i know there are other solutions like the ROW_Number() but wanted to explore other techniques, if you think FOR XML PATH('') would work, can you show an example using my student table provided?
thanks for your input and question sgmunson, any info would only help me learn and grow 🙂
June 19, 2018 at 10:58 am
Siten0308 - Tuesday, June 19, 2018 10:48 AMHello sgmunson,
thanks for getting back to me,i am doing this to help eliminate duplicate rows when i join it back to another table, which the table is called classes, the classes table is just class information. but when i Left join it, sometimes it causes duplicates, because there was 1 other thing i forgot to mention, and sad to say... they way the table structure is... its joining the tables on varchar columns and the to foreign keys or both varchar to join the 2 tables together, the students and classes table, plus i also forgot to mention, there is a data dictionary table, so i the joining table would look like:
Student --> join to ---> data dictionary table --> join to --> classes
in order to join correctly... but i wanted to make my example simple, as the objective is to get the student table into possibly a json format that can hopefully solve some duplicate issue.
i know there are other solutions like the ROW_Number() but wanted to explore other techniques, if you think FOR XML PATH('') would work, can you show an example using my student table provided?thanks for your input and question sgmunson, any info would only help me learn and grow 🙂
Okay, JSON is probably NOT going to help you here. Getting duplicates by virtue of your JOINs and a poorly designed table structure may be difficult to avoid, but it shouldn't be impossible, even if it ends up somewhat impractical. However, we'll need a lot more details, as this is a case where a simplified version of the problem isn't going to help. We'll need table create statements for the three tables involved, along with sample data for all 3 tables, coded as INSERT statements, and finally, the expected results based on the sample data you provide. It also would be helpful to have a detailed description of what a row in each of the tables actually represents in the real world.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply