Monthly Archives: February 2018

Python use case – Convert rows into comma separated values in a column – SQL Server 2017

In this post, we are going to learn how we can leverage python in SQL server to generate comma separated values.

If we want to combine all values of a single column it is fairly easy as we can use COALESCE function to do that. Here is a reference to the already existing post. But have you ever thought what would happen if we needed a comma separated value in a column along with other columns? In that scenario, this approach would not work.

We can get comma separated values in a column along with other columns using FOR XML PATH  query wrapped inside a sub-query, but there also we would need to take care of HTML encoded characters like < and >.

Now, with python’s integration with SQL Server 2017, it can be achieved very easily and efficiently as we do not have to rely on subqueries and … More

Python use case – Dynamic UNPIVOT using pandas – SQL Server 2017 1

In this post, we are going to learn how we can leverage the power of Python’s pandas module in SQL Server 2017. pandas is an open source Python library providing data frame as data structure similar to the SQL table with the vectorized operation support for high performance. To know more about pandas, you can click here.

Let’s discuss the problem we face while using the SQL UNPIVOT clause especially when we have a large number of columns. We can use UNPIVOT clause in SQL Server to convert the columns as row values and normalize the output result set. To use the UNPIVOT command, we need to specify each column name as a fixed value while writing the T-SQL query. However, this becomes annoying if we need to specify a large number of columns in the UNPIVOT clause. Also, if the column names are not fixed (dynamic in nature), … More