none
how can grouped more than one rows in sql RRS feed

  • Question

  • In education database I have a view that it shows the courses. And
    I have defined several sessions for some courses. now I want to show this session of the unit that has the same <g class="gr_ gr_9 gr-alert gr_spell gr_disable_anim_appear ContextualSpelling ins-del multiReplace" data-gr-id="9" id="9">CoursId</g> together. can I use GroupBy for this ?!
    how can I do this correctly?!
    Sunday, December 23, 2018 7:44 AM

Answers

  • in the second pic, I want to Show two rows in a row and in the first one Each one is shown in a separate row.

    That is nothing you can achieve with a query. Here is an important lesson: a relational database engine like SQL Server returns rows to the client. These rows may be ordered if you and an ORDER BY clause, but it still a set of rows that all have the shape and form. If you want these rows to be formatted in some way, for instance having a dividing line added when a certain column changes value, this is something you set up in your presentation layer, be that Excel, PowerBI or some other report writer. If you only use a very simple presentation tool like SQL Server Management Studio, such a feature may not be available. (SSMS does not have such a functionality.)

    And I like to stress again that this is indeed an important lesson. When working with databases, you need to understand what they are good at and what is outside their scope. They are very good at manipulating and finding data, including analsysing it with powerful queries. They are not general-purpose programming environments. They are not intended for reading or writing files from disk. And they are not intended to format your output. For this purpose you use other tools.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by sa ba Wednesday, December 26, 2018 9:13 AM
    Tuesday, December 25, 2018 9:33 AM

All replies

  • I think it would help if you could clarify your question by posting sample data and the expected result from the sample data. Best is if you can post CREATE TABLE + INSERT, as then we can copy and paste into SSMS to develop a tested solution.

    It would also help if you turn off the Chrome spellchecker (I believe that is the culprit), as it does not interact well with the poor posting UI of this forum, as you can see in your original post.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, December 23, 2018 10:34 AM
  • In education database I have a view that it shows the courses. And
    I have defined several sessions for some courses. now I want to show this session of the unit that has the same CoursId together. can I use GroupBy for this ?!
    how can I do this correctly?!

    Hi sa ba,

    Thanks for posting here.

    Per your description, I guess you need to use EXISTS operator to get expected results. But I am not sure what your sample data is like. So I could only use some sample data to express this.

     create table courses
     (
      identiId int identity(1,1),
      CoursId int,
      sessionid int,
      session_name varchar(30)
    )
    
    insert into courses values
    (112,334,'session1'),
    (112,334,'session1'),
    (113,21,'session2'),
    (113,22,'session3')
    
    --For specific session
    select * from courses T
    where exists (select 1 from courses where sessionid=T.sessionid and CoursId=T.CoursId and identiId!=T.identiId)
    and T.session_name='session1'
    --Output
    /*
    identiId    CoursId     sessionid   session_name
    ----------- ----------- ----------- ------------------------------
    1           112         334         session1
    2           112         334         session1
    */

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 24, 2018 2:47 AM
    Moderator
  • Hi Will_Kong
    thx for your solution, for example, consider the following picture

    I highlight CourseId rows that have the same value.
    How can I shows those rows that have the same CourseId together?!
    Monday, December 24, 2018 8:40 AM
  • Hi Will_Kong
    thx for your solution, for example, consider the following picture

    I highlight CourseId rows that have the same value.
    How can I shows those rows that have the same CourseId together?!

    Hi sa ba,

    How about this query statement?

    ;WITH CTE AS
    (
    SELECT 
    COUNT(1) OVER (PARTITION BY CourseId) as v_count,
    ST,
    ET,
    DayWeek,
    TRoom,
    TSemester,
    CourseId,
    SubjectStudyId,
    Code
    FROM YOUR_TABLE T
    )
    SELECT * FROM CTE
    WHERE  v_count>1
    ORDER BY CourseId

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 24, 2018 8:53 AM
    Moderator
  • Thx Dear Will_kong

    the result of your code is this:

    but  want this result:



    • Edited by sa ba Monday, December 24, 2018 9:45 AM
    Monday, December 24, 2018 9:32 AM
  • Thx Dear Will_kong

    the result of your code is this:

    but <g class="gr_ gr_5 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" data-gr-id="5" id="5">i</g> want this result:


    So the query is like this?

    ;WITH CTE AS
    (
    SELECT 
    COUNT(1) OVER (PARTITION BY CourseId) as v_count,
    ST,
    ET,
    DayWeek,
    TRoom,
    TSemester,
    CourseId,
    SubjectStudyId,
    Code
    FROM YOUR_TABLE T
    )
    SELECT 
    ST,
    ET,
    DayWeek,
    TRoom,
    TSemester,
    CourseId,
    SubjectStudyId,
    Code
    FROM CTE
    ORDER BY CourseId,v_count DESC

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 24, 2018 9:40 AM
    Moderator
  • the first picture is the result of your query. and for the second picture :

    I did not write any query to that, I just wrote what I wanted on a table.

    Monday, December 24, 2018 9:49 AM
  • As long as you only post screenshots you will only get guesses of what you are looking for. If you want a  tested query, you should do as I said in my previous post: post the CREATE TABLE statement for your table, and INSERT statements with sample data. The expected result you post as a screen shot.

    I will have to say that I don't really understand the difference between the result you got from Will's query and the result you wanted. Well, your expected result has one row more, because Will added a filter on the count, but it seems that is latter query should address this.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, December 24, 2018 10:01 AM
  • the first picture is the result of your query. and for the second picture :

    I did not write any query to that, I just wrote what I wanted on a table.

    The table data rows are unordered by default. If what you wrote is that the same CourseId rows are not put together, you have to write that query with ORDER BY clause to get expected results.

    Hope you could notice that.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 25, 2018 3:07 AM
    Moderator
  • Hi Dear Erland Sommarskog,

    thx for your guidance. my table is the result of view with more than 20 tablesbecause I did not give my tables. and the difference  between  the result I got from Will's query and the result that I wanted is :

    in the second pic, I want to Show two rows in a row and in the first one Each one is shown in a separate row.




    • Edited by sa ba Tuesday, December 25, 2018 8:49 AM
    Tuesday, December 25, 2018 6:17 AM
  • in the second pic, I want to Show two rows in a row and in the first one Each one is shown in a separate row.

    That is nothing you can achieve with a query. Here is an important lesson: a relational database engine like SQL Server returns rows to the client. These rows may be ordered if you and an ORDER BY clause, but it still a set of rows that all have the shape and form. If you want these rows to be formatted in some way, for instance having a dividing line added when a certain column changes value, this is something you set up in your presentation layer, be that Excel, PowerBI or some other report writer. If you only use a very simple presentation tool like SQL Server Management Studio, such a feature may not be available. (SSMS does not have such a functionality.)

    And I like to stress again that this is indeed an important lesson. When working with databases, you need to understand what they are good at and what is outside their scope. They are very good at manipulating and finding data, including analsysing it with powerful queries. They are not general-purpose programming environments. They are not intended for reading or writing files from disk. And they are not intended to format your output. For this purpose you use other tools.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by sa ba Wednesday, December 26, 2018 9:13 AM
    Tuesday, December 25, 2018 9:33 AM