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