SQL SERVER – Group by Rows and Columns using XML PATH – Efficient Concating Trick

I hardly get hard time to come up with the title of the blog post. This was one of the blog post even though simple, I believe I have not come up with appropriate title. Any way here is the question I received.

“I have a table of students and the courses they are enrolled with the name of the professor besides it. I would like to group the result with course and instructor name.

For example here is my table:

How can I generate result as following?

Now you can see how easy the question is but so hard to come up with either solution or  title of this blog post. We can use XML PATH and come up with the solution where we combine two or more columns together and display desired result.

Here is the quick script which does the task ask, I have used temporary tables so you can just take this script and quickly run on your machine and see how it returns results.

Let me know if there are any better ways to do the same.

-- Create table
CREATE TABLE #TestTable (StudentName VARCHAR(100), Course VARCHAR(100), Instructor VARCHAR(100), RoomNo VARCHAR(100))
GO
-- Populate table
INSERT INTO #TestTable (StudentName, Course, Instructor, RoomNo)
SELECT 'Mark', 'Algebra', 'Dr. James', '101'
UNION ALL
SELECT 'Mark', 'Maths', 'Dr. Jones', '201'
UNION ALL
SELECT 'Joe', 'Algebra', 'Dr. James', '101'
UNION ALL
SELECT 'Joe', 'Science', 'Dr. Ross', '301'
UNION ALL
SELECT 'Joe', 'Geography', 'Dr. Lisa', '401'
UNION ALL
SELECT 'Jenny', 'Algebra', 'Dr. James', '101'
GO
-- Check orginal data
SELECT *
FROM #TestTable
GO
-- Group by Data using column and XML PATH
SELECT
StudentName,
STUFF((
SELECT ', ' + Course + ' by ' + CAST(Instructor AS VARCHAR(MAX)) + ' in Room No ' + CAST(RoomNo AS VARCHAR(MAX))
FROM #TestTable
WHERE (StudentName = StudentCourses.StudentName)
FOR XML PATH (''))
,
1,2,'') AS NameValues
FROM #TestTable StudentCourses
GROUP BY StudentName
GO
-- Clean up
DROP TABLE #TestTable
GO

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Grouping by Multiple Columns to Single Column as A String

One of the most common questions I receive in email is how to group multiple column data in comma separate values in a single row grouping by another column.

I have previously blogged about it in following two blog posts. However, both aren’t addressing the following exact problem.

The question comes in many different formats but in following image I am demonstrating the same question in simple words. This is the most popular question on my Facebook page as well. (Example)

Here is the sample script to build the sample dataset.

CREATE TABLE TestTable (ID INT, Col VARCHAR(4))
GO
INSERT INTO TestTable (ID, Col)
SELECT 1, 'A'
UNION ALL
SELECT 1, 'B'
UNION ALL
SELECT 1, 'C'
UNION ALL
SELECT 2, 'A'
UNION ALL
SELECT 2, 'B'
UNION ALL
SELECT 2, 'C'
UNION ALL
SELECT 2, 'D'
UNION ALL
SELECT 2, 'E'
GO
SELECT *
FROM TestTable
GO

Here is the solution which will build an answer to the above question.

-- Get CSV values
SELECT t.ID, STUFF(
(
SELECT ',' + s.Col
FROM TestTable s
WHERE s.ID = t.ID
FOR XML PATH('')),1,1,'') AS CSV
FROM TestTable AS t
GROUP BY t.ID
GO

I hope this is an easy solution. I am going to point to this blog post in the future for all the similar questions.

Final Clean Up Act
-- Clean up
DROP TABLE TestTable
GO

Here is the question back to you -
Is there any better way to write above script? Please leave a comment and I will write a separate blog post with due credit.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – View XML Query Plans in SSMS as Graphical Execution Plan

Earlier I wrote a blog post on SQL SERVER – Parallelism – Row per Processor – Row per Thread, where I mentioned the XML Plan. As a follow up on the blog post, I received the request to send the same execution plan so that the blog readers can also use the same and reproduce it on their machine. I realized that I have actually never written on how one can send a graphical execution plan to another user so that they can reproduce the same exact details without all the actual tables, indexes and objects.

Here is very simple method on how one can do that.

Right Click on Execution Plan and click on “Save Execution Plan As…”.

You can save the plan with the extension .sqlplan. The same plan can be sent to another user via email or a USB drive. Another user can just double click on the file and open the execution plan at another local computer without physically having any underlying object.

This is very simple trick; you can also send the execution plan in text format as well. We will talk about it in some other post.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Guest Post – SELECT * FROM XML – Jacob Sebastian

One of the most common problem SQL Server developers face while dealing with XML is related to writing the correct XPath expression to read a specific value from an XML document. I usually get a lot of questions by email, on my blog or in the forums which looks like the following:

“I have the following XML document and I am trying to read the value from xyz node. When I run my query, I get a NULL value”

My friend Jacob Sebastian (SQL Server MVP) has written excellent article on the subject SELECT * FROM XML, I strongly recommend to either bookmark it and read it before continuing further in this article.

In most cases, I have seen that the problem was due to an incorrect XPath expression. XPath expressions are not complicated at all, but they need a close attention to get them right. Assume that we have the following XML fragment.

Looking at the structure of the XML document, it is quite easy to figure out the XPath expression pointing to each element and attribute. The following illustration shows how to do this.

One of the simplest ways is to identify the nodes with their position as given in the above illustration. A more detailed listing is given below.

The above example also used the “position” of the elements to uniquely identify them. In real life you might need to have more complex matching criteria such as the “email of the Employee element whose id is 10001”. The following example shows how to apply this type of filters.

/Employees/Employee[@id=”1001″]/Email

In most cases, you will be able to easily build your XPath expressions. However, if you find it difficult, you can take help from my helper function given here. This function allows you to run ‘blind’ queries on the XML document very similar to the ‘select * from table’ queries that we usually run on unknown tables.

For example, if you would like to quickly examine the above XML document and see the elements, attributes and their XPath expression, you can execute something like the following:

Make sure that you create the function XMLTable() using the script given in the above URL.

Once you have the output of the function, you can copy the XPath expressions from the results and use in your Queries. For example, if you are looking for the email address of Pinal, you can just copy the expression from row 8 (highlighted in the image given above) and use in your query as:

SELECT @x.value(‘Employees[1]/Employee[2]/Email[1]’,’VARCHAR(50)’)

I hope you will find this post interesting and the XMLTable() function might help you to solve some of the XML querying problems you may face in your SQL Server Journey. If you have got any question about XML in general, or about this function in particular, please feel free to post them on the XML forum and I will try my best to help you out.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Converting a Delimited String of Values into Columns

This blog post is about two great bloggers and their excellent series of blog posts. It was quite unusual to see two bloggers posting articles that are supporting each other and constantly improving the articles to the next level.

Two blogs which I am going to mention here are as follows: SELECT Blog FROM Brad.Schulz CROSS APPLY SQL.Server() – Brad Schulz and Demystifying SQL Server – Adam Haines. Before continuing this blog post, I suggest you all to bookmark these blogs for future reference.

The whole thing started when Adam tried to answer the question “How to transform a delimited values into columns?” on MSDN SQL Forum. Adam made the first blog post here http://jahaines.blogspot.com/2009/06/converting-delimited-string-of-values.html and then Brad and Adam bounced a few ideas off the wall. Then, Adam started his series on concatenating column values http://jahaines.blogspot.com/2009/06/concatenating-column-values-part-1.html, and http://jahaines.blogspot.com/2009/07/concatenating-column-values-part-2.html.  In part two of the series, Brad wanted to get a deeper understanding of why the results came out the way they did. Adam then started another series on unpacking or parsing out delimited characters, http://jahaines.blogspot.com/2009/11/splitting-delimited-string-part-1.html and here http://jahaines.blogspot.com/2009/12/splitting-delimited-string-part-2.html.  From here, Brad started digging even deeper into the internals of the XML method. Brad has a comprehensive list of the back and forth on this post, http://bradsruminations.blogspot.com/2009/12/delimited-string-tennis-anyone.html and a final post here http://bradsruminations.blogspot.com/2010/01/delimited-string-tennis-again-final.html.

The final outcome is that both the XML method and number table can perform equally, but the XML method has to be coded in a very particular way; otherwise performance will be horrendous. Also, the permanent number table shows consistently more performance than the inline number table; however, on smaller string sizes, the difference here is that most of the inline number tables require more CPU and RAM, whereas the permanent number table requires more IO. For an average developer, the number table solution is probably the easiest of the solutions to implement.

Overall, I strongly suggest to go through the abovementioned posts; you will love it and become their fan. Please note these posts are very easy to understand and if you know a bit of XML only, you will still be able to understand them very well. The above description has been taken with proper consent of the Adam and Brad.

Hats Off to you Guys! You guys inspire me and many SQL enthusiasts!

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Comma Separated Values (CSV) from Table Column – Part 2

In my earlier post, I wrote about how one can use XML to convert table to string SQL SERVER – Comma Separated Values (CSV) from Table Column. The same article is also published on channel 9 SQLAuthority News – Featured on Channel 9. One of the very interesting points that was discussed on show was about the usage of function SUBSTRING. I found the following point very valid: SUBSTRING usage limits the length of the XML to be used.

I have re-written the same function with function STUFF, and it removes any limit imposed on the script.

USE AdventureWorks
GO
-- Check Table Column
SELECT [Name]
FROM HumanResources.Shift
GO
-- Get CSV values
SELECT STUFF(
(SELECT ',' + s.Name
FROM HumanResources.Shift s
ORDER BY s.Name
FOR XML PATH('')),1,1,'') AS CSV
GO

Do let me know your thoughts on the same.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Validate an XML document in TSQL using XSD by Jacob Sebastian

Following article is sent by SQL Server MVP Jacob Sebastian on request to provide solution for validating XML document.

XML Schema Collections

Because of the ‘eXtensible’ nature of XML (eXtensible Markup Language), often there is a requirement to restrict and validate the content of an XML document to a pre-defined structure and values. XSD (XML Schema Definition Language) is the W3C recommended language for describing and validating XML documents. SQL Server implements XSD as XML Schema Collections.

An XML Schema Collection can be used to validate an XML document stored in an XML column or variable.  To understand this better, let us see an example.

XML Document

For the purpose of this example, let us create an XML Schema Collection to validate the following XML Document.

<Employee>
    <FirstName>Jacob</FirstName>
    <MiddleName>V</MiddleName>
    <LastName>Sebastian</LastName>
</Employee>

Validation Rules

Here are the validation rules we are trying to implement.

  1. The root element should be named ‘Employee’
  2. The root element should have three child elements, named ‘FirstName’, ‘MiddleName’ and ‘LastName’.
  3. Child elements should appear exactly in the order given above.

Creating the XML Schema Collection

Here is the XML Schema collection that performs the above validations.

CREATE XML SCHEMA COLLECTION EmployeeSchema
    AS'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element >
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element />
                <xsd:element />
                <xsd:element />
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>'

Validating an XML variable

The following code validates an XML document against the rules defined in the XML Schema Collection.

DECLARE @x XML(EmployeeSchema)
SELECT @x = '<Employee>
    <FirstName>Jacob</FirstName>
    <MiddleName>V</MiddleName>
    <LastName>Sebastian</LastName>
</Employee>'

Note that the operation will succeed only if all the validation rules defined in the XML schema collection are met. For example, the following will fail because the XML document is missing the ‘MiddleName’ element.

DECLARE @x XML(EmployeeSchema)
SELECT @x = '<Employee>
    <FirstName>Jacob</FirstName>
    <LastName>Sebastian</LastName>
</Employee>'

Please read further details on the same subject over SyntaxHelp XML.

Reference: Pinal Dave (http://blog.SQLAuthority.com)