{"id":2479,"date":"2019-08-26T06:14:31","date_gmt":"2019-08-26T06:14:31","guid":{"rendered":"https:\/\/www.testpreptraining.com\/tutorial\/?page_id=2479"},"modified":"2022-03-17T05:13:22","modified_gmt":"2022-03-17T05:13:22","slug":"group-and-pivot-data-by-using-queries","status":"publish","type":"page","link":"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/group-and-pivot-data-by-using-queries\/","title":{"rendered":"Group and pivot data by using queries"},"content":{"rendered":"\n

Exam 70-761: Querying Data with Transact-SQL retired on 31 January, 2021.<\/strong><\/h2>\n\n\n\n

Ranking Functions (Transact-SQL)<\/strong><\/h3>\n\n\n\n

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic. <\/p>\n\n\n\n

Transact-SQL provides the following ranking functions:<\/p>\n\n\n\n

  • RANK NTILE<\/li>
  • DENSE_RANK ROW_NUMBER<\/li><\/ul>\n\n\n\n

    SELECT – GROUP BY- Transact-SQL<\/strong><\/h3>\n\n\n\n

    A SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group. The SELECT statement returns one row per group.<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n
    \"\"<\/figure>\n\n\n\n

    OUTPUT Clause (Transact-SQL)<\/strong><\/h3>\n\n\n\n

    Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view. Which is used in – <\/p>\n\n\n\n

    • DELETE<\/li>
    • INSERT<\/li>
    • UPDATE<\/li>
    • MERGE<\/li><\/ul>\n\n\n\n
      \"\"<\/figure>\n\n\n\n

      output_table<\/strong><\/h4>\n\n\n\n

      Specifies a table that the returned rows are inserted into instead of being returned to the caller. output_table may be a temporary table.<\/p>\n\n\n\n

      If column_list is not specified, the table must have the same number of columns as the OUTPUT result set. The exceptions are identity and computed columns. These must be skipped. If column_list is specified, any omitted columns must either allow null values or have default values assigned to them.<\/p>\n\n\n\n

      output_table cannot:<\/p>\n\n\n\n

      • Have enabled triggers defined on it.<\/li>
      • Participate on either side of a FOREIGN KEY constraint.<\/li>
      • Have CHECK constraints or enabled rules.<\/li><\/ul>\n\n\n\n

        column_list<\/strong><\/h4>\n\n\n\n

        Is an optional list of column names on the target table of the INTO clause. It is analogous to the column list allowed in the INSERT statement.<\/p>\n\n\n\n

        scalar_expression<\/strong><\/h4>\n\n\n\n

        Is any combination of symbols and operators that evaluates to a single value. Aggregate functions are not permitted in scalar_expression. Any reference to columns in the table being modified must be qualified with the INSERTED or DELETED prefix.<\/p>\n\n\n\n

        column_alias_identifier<\/strong><\/h4>\n\n\n\n

        Is an alternative name used to reference the column name.<\/p>\n\n\n\n

        DELETED<\/strong><\/h4>\n\n\n\n

        Is a column prefix that specifies the value deleted by the update or delete operation. Columns prefixed with DELETED reflect the value before the UPDATE, DELETE, or MERGE statement is completed.<\/p>\n\n\n\n

        DELETED cannot be used with the OUTPUT clause in the INSERT statement.<\/p>\n\n\n\n

        INSERTED<\/strong><\/h4>\n\n\n\n

        Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed. INSERTED cannot be used with the OUTPUT clause in the DELETE statement.<\/p>\n\n\n\n

        from_table_name<\/strong><\/h4>\n\n\n\n

        Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.<\/p>\n\n\n\n

        If the table being modified is also specified in the FROM clause, any reference to columns in that table must be qualified with the INSERTED or DELETED prefix.<\/p>\n\n\n\n

        Note, Specifies that all columns affected by the delete, insert, or update action will be returned in the order in which they exist in the table.<\/p>\n\n\n\n

        FROM – Using PIVOT and UNPIVOT<\/strong><\/h3>\n\n\n\n

        You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And PIVOT runs aggregations where they’re required on any remaining column values that are wanted in the final output. UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.<\/p>\n\n\n\n

        The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT\u2026CASE statements. For a complete description of the syntax for PIVOT, see FROM (Transact-SQL).<\/p>\n\n\n\n

        Syntax<\/strong>
        The following syntax summarizes how to use the PIVOT operator.<\/p>\n\n\n\n

        \"\"<\/figure>\n\n\n\n

        Note – <\/strong>The column identifiers in the UNPIVOT clause follow the catalog collation. For SQL Database, the collation is always SQL_Latin1_General_CP1_CI_AS. For SQL Server partially contained databases, the collation is always Latin1_General_100_CI_AS_KS_WS_SC. If the column is combined with other columns, then a collate clause (COLLATE DATABASE_DEFAULT) is required to avoid conflicts.<\/p>\n\n\n\n

        Replacing NULL value by 0 in the Dynamic Pivot Result – SQL Server<\/strong><\/h3>\n\n\n\n

        Let us consider the below example to elaborate –<\/p>\n\n\n\n

        \"\"<\/figure>\n\n\n\n

        Script to create the temporary table #CourseSales table with sample data as depicted in the given image: <\/p>\n\n\n\n

        \"\"<\/figure>\n\n\n\n

        The Dynamic PIVOT script to generate the PIVOT result with NULL value –<\/p>\n\n\n\n

        \"\"<\/figure>\n\n\n\n

        Result for the above Query<\/strong><\/p>\n\n\n\n

        \"\"<\/figure>\n\n\n\n

        Now we can modify the previous query like below to replace NULL by 0 in the dynamic PIVOT query result –<\/p>\n\n\n\n

        \"\"<\/figure>\n\n\n\n

        Result<\/strong><\/p>\n\n\n\n

        \"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"

        Exam 70-761: Querying Data with Transact-SQL retired on 31 January, 2021. Ranking Functions (Transact-SQL) Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic. Transact-SQL provides the following ranking functions: RANK NTILE…<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1931,"menu_order":9,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_acf_changed":false,"footnotes":""},"categories":[],"tags":[360,369,357],"class_list":["post-2479","page","type-page","status-publish","hentry","tag-exam-70-761","tag-microsoft-70-761-exam-dumps","tag-microsoft-exam"],"acf":[],"yoast_head":"\nGroup and pivot data by using queries - Testprep Training Tutorials<\/title>\n<meta name=\"description\" content=\"Learn more about Group and pivot data by using queries and advanced practice test to become a Microsoft certified professional. Try Exam 70-761 Now!\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/group-and-pivot-data-by-using-queries\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Group and pivot data by using queries - Testprep Training Tutorials\" \/>\n<meta property=\"og:description\" content=\"Learn more about Group and pivot data by using queries and advanced practice test to become a Microsoft certified professional. Try Exam 70-761 Now!\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/group-and-pivot-data-by-using-queries\/\" \/>\n<meta property=\"og:site_name\" content=\"Testprep Training Tutorials\" \/>\n<meta property=\"article:modified_time\" content=\"2022-03-17T05:13:22+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.testpreptraining.ai\/tutorial\/wp-content\/uploads\/2019\/08\/image-205-521x400.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/group-and-pivot-data-by-using-queries\/\",\"url\":\"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/group-and-pivot-data-by-using-queries\/\",\"name\":\"Group and pivot data by using queries - Testprep Training Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#website\"},\"datePublished\":\"2019-08-26T06:14:31+00:00\",\"dateModified\":\"2022-03-17T05:13:22+00:00\",\"description\":\"Learn more about Group and pivot data by using queries and advanced practice test to become a Microsoft certified professional. Try Exam 70-761 Now!\",\"breadcrumb\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/group-and-pivot-data-by-using-queries\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/group-and-pivot-data-by-using-queries\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/group-and-pivot-data-by-using-queries\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.testpreptraining.ai\/tutorial\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Exam 70-761: Querying Data with Transact-SQL\",\"item\":\"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Group and pivot data by using queries\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#website\",\"url\":\"https:\/\/www.testpreptraining.ai\/tutorial\/\",\"name\":\"Testprep Training Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.testpreptraining.ai\/tutorial\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#organization\",\"name\":\"Testprep Training\",\"url\":\"https:\/\/www.testpreptraining.ai\/tutorial\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png\",\"contentUrl\":\"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png\",\"width\":583,\"height\":153,\"caption\":\"Testprep Training\"},\"image\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Group and pivot data by using queries - Testprep Training Tutorials","description":"Learn more about Group and pivot data by using queries and advanced practice test to become a Microsoft certified professional. Try Exam 70-761 Now!","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/group-and-pivot-data-by-using-queries\/","og_locale":"en_US","og_type":"article","og_title":"Group and pivot data by using queries - Testprep Training Tutorials","og_description":"Learn more about Group and pivot data by using queries and advanced practice test to become a Microsoft certified professional. Try Exam 70-761 Now!","og_url":"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/group-and-pivot-data-by-using-queries\/","og_site_name":"Testprep Training Tutorials","article_modified_time":"2022-03-17T05:13:22+00:00","og_image":[{"url":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-content\/uploads\/2019\/08\/image-205-521x400.png"}],"twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/group-and-pivot-data-by-using-queries\/","url":"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/group-and-pivot-data-by-using-queries\/","name":"Group and pivot data by using queries - Testprep Training Tutorials","isPartOf":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#website"},"datePublished":"2019-08-26T06:14:31+00:00","dateModified":"2022-03-17T05:13:22+00:00","description":"Learn more about Group and pivot data by using queries and advanced practice test to become a Microsoft certified professional. Try Exam 70-761 Now!","breadcrumb":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/group-and-pivot-data-by-using-queries\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/group-and-pivot-data-by-using-queries\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/group-and-pivot-data-by-using-queries\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.testpreptraining.ai\/tutorial\/"},{"@type":"ListItem","position":2,"name":"Exam 70-761: Querying Data with Transact-SQL","item":"https:\/\/www.testpreptraining.ai\/tutorial\/exam-70-761-querying-data-with-transact-sql\/"},{"@type":"ListItem","position":3,"name":"Group and pivot data by using queries"}]},{"@type":"WebSite","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#website","url":"https:\/\/www.testpreptraining.ai\/tutorial\/","name":"Testprep Training Tutorials","description":"","publisher":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.testpreptraining.ai\/tutorial\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#organization","name":"Testprep Training","url":"https:\/\/www.testpreptraining.ai\/tutorial\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/","url":"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png","contentUrl":"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png","width":583,"height":153,"caption":"Testprep Training"},"image":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/"}}]}},"_links":{"self":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages\/2479","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/comments?post=2479"}],"version-history":[{"count":7,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages\/2479\/revisions"}],"predecessor-version":[{"id":53010,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages\/2479\/revisions\/53010"}],"up":[{"embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages\/1931"}],"wp:attachment":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/media?parent=2479"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/categories?post=2479"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/tags?post=2479"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}