T-SQL: Convert Rows into delimited string

While working on .Net, SSRS or any other application, most of the time we came into a situation where we need table rows into a delimited string in sql output.

Let’s create a table with some data to illustrate this scenario.

Create table #Organization(Org_Id Int Identity,Org_Name varchar(100))

Go

Insert Into #Organization(Org_Name)Values

('AdventureWorks Cycle'),('North America Operations'),

('Northeast Division'),('Central Division'), ('France'),('USA Operations')

Go

Select Org_Id,Org_Name from #Organization

We have multiple ways to achieve the desire output. One of the way is by using
Coalesce() in-build function.

Declare @Output varchar(Max)

SELECT @Output=Coalesce(@Output+Org_Name+',',Org_Name+',') FROM #Organization

Select @Output as OutputValue

Another way is by using XML clause.

Declare @Output varchar(Max)

Set @Output=(SELECT Org_Name+',' FROM #Organization for XML path (''))

Select @Output as OutputValue

Point to remember during using this method is that if your column contains xml markup characters like “&”,”<”,”>” etc then you will get some unexpected output. In this sitution, you need to use Replace() method to get the expected output. To test this problem, add one more record in the #Organization table with value ‘North & South zone’ and test the above sql. In the output, you will notice “North &amp; South Zone” instead of “North & South Zone”

Last but not then least one is by using vairble in T-sql. Other ways are cursor, looping concept etc. that can be used depending on the requirement or sitution.

Declare @Output varchar(Max)

SELECT @Output=ISNULL(@Output,'')+Org_Name+',' FROM #Organization

Select @Output as OutputValue

Point to strongly remember is that NULL column values will result NULL or unexpected result in T-Sql output. So NULL must be handle before using using any of the approach.

SSRS : Remove non required columns and give appropriate column name in CSV output

In case of CSV export, we always need to give some additional treatment to get the desire output in CSV. Most of the time, we face the issue with the unwanted column in CSV export.

Let’s take a sample report example with columns EmployeeCode, FirstName, LastName, Email and Phone

Here we will show Email and Phone in one column and will give an expression to column header that will be ="EMail "+VBCRLF+"Phone"

Now execute the report and export it into CSV format. I am getting this output in CSV format

In the output, we notice 2 issues.

1.       Unwanted column A in CSV.

2.       How to give an appropriate column name to column E. Currently it is showing Textbox5

To resolve the unwanted column in CSV output, select that cell/textbox in report design and set NoOutput in DataElementOutput in property window. In our scenario, we will set NoOutput to “Email Phone” column header.

To resolve our 2^nd issue, we have following two ways:

·         Select the data cell/textbox (Not the header cell/textbox) and give appropriate value to Name property or

·         Select the data cell/textbox (Not the header cell/textbox) and open the property window and give appropriate value to DataElelmentName property

In our scenario, we are giving “Email_Phone” as a value to DataElelmentName property of the “Email Phone” data cell/textbox.

Now let’s execute the report and export the output in CSV.

I am getting this output in CSV format that looks fine now.

To achieve required CSV output, we need to give attention to following points while designing the report:

1.      DataElementName Property: Use this property to get expected column name in CSV. CSV export format consider s data cell/textbox Name or DataElementName property as column name. Since it is compulsory to give Name to all cells/textboxes. But if you give value to DataElementName property then CSV format will consider DataElementName value instead of cell/textbox Name.

2.      DataElementOutput Property : this property has three values for selection.

Auto: - If SSRS consider a cell/textbox as a plain header cell/textbox then  that cell/textbox will not be exported in CSV format.

Output: - If it is compulsory for you to show a particular cell/textbox in CSV export then use this option.

NoOutput: - It is opposite to Output option.

Sample RDL can be downloaded from https://docs.google.com/open?id=0ByYnpeuIfYV0OUVVcERMSm1NRU0

Single comma separated row into multiple rows

1.Single comma separated row into multiple rows.
Lets create table
  Create table #temp (Country varchar(15), City varchar(50))
  Insert into #temp Values ('INDIA','Pune, New Delhi,Mumbai, Chandigarh')
  Insert into #temp Values ('USA','New York, New Jersey,Washington, California')
lets see the Out of this table below
#Output1:-
Country   City
INDIA     Pune, New Delhi,Mumbai, Chandigarh
USA        New York, New Jersey,Washington, California
Excecute this query
SELECT temp.Country,     
Split.temp.value('.', 'VARCHAR(20)') AS city
FROM  (SELECT Country,           CAST ('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String FROM  #temp) AS temp  CROSS APPLY String.nodes ('/M') AS Split(temp);

Output2:-
Country    city
INDIA     Pune
INDIA     New Delhi
INDIA     Mumbai
INDIA     Chandigarh
USA         New York
USA         New Jersey
USA         Washington
USA          California
2.Multiple rows into a single comma separated row as #Output1

Now you put the output2 in another table  i.e #temp2
SELECT temp.Country,     
Split.temp.value('.', 'VARCHAR(20)') AS city
into #temp2
FROM  (SELECT Country, CAST ('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String       FROM  #temp) AS temp  CROSS APPLY String.nodes ('/M') AS Split(temp);

Now Select This Query

select distinct  t1.Country, (
select t.City + ','
from #temp2 t
where t.Country=t1.Country
order by t.City
for xml path( '' )
)

from #temp2  t1

SQL show running queries

SQL show running queries

We sometimes need to find out running queries on sql server box. To show running queries using t-sql I use the syntax below

SELECT *
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)