Tag : row concatenation


Convert rows into comma separated values in a column 9

In this post, we are going to learn a technique to combine all the values of a row (based on a particular condition) with a separator, in a column along with other columns. What i meant by this let me explain it in more detail. Have a look on below question as raised by a colleague;

I have a Students table and a Subjects table. One student has been assigned to multiple Subjects in a transaction table. I want to extract the name of all the Subjects for which the student is assigned in a comma separated way along with other details of the student. If student is not assigned yet, i want to show a “Not Assigned Yet” value for those records.

To achieve this task, i have created a demo script and sharing the same with all the details. Below you can find the table structures and demo … More


Avoid Use of While Loops to get a Comma Seperated list of rows 1

Problem Detail

In the morning at your workplace, your manager or any colleague comes to you and asks to get the list of all the dates in a comma separated list.

Now the first and the quickest way of getting the output which will knock us is using a loop to get the list.

/*Getting list with While loop*/

DECLARE @fromdate DATETIME; SET @fromdate = '01-Sep-2014'

DECLARE @todate DATETIME; SET @todate = '30-Sep-2014'

DECLARE @vc_PVTColumns VARCHAR(1000); SET @vc_PVTColumns = ''

WHILE (@fromdate <= @todate)

BEGIN

SET @vc_PVTColumns = @vc_PVTColumns + '[' + CONVERT(VARCHAR(25), @fromdate, 106) + '], '

SET @fromdate = DATEADD(d, 1, @fromdate)

END

SELECT @vc_PVTColumns =LEFT(@vc_PVTColumns, LEN(@vc_PVTColumns) - 1)

SELECT @vc_PVTColumns

In above case,¬†DB engine has to iterate for each and every value of day and do the same task again and again. The same thing can be implementedin SET based approach using COALESCE function with a … More