CODEWITHSUNDEEP
reporting-servicessplit
Balu Challa
Balu Challa

Reputation: 11

How to split single column data into multiple columns in ssrs reports?

I have one table as shown in bellow

EmpID Name 1 Ram,Shaker,Sarath 2 Raju,Ravi

enter image description here

I need output as

EmpID Name Name 1 Ram Shaker,Sarath 2 Raju Ravi

Upvotes: 0

Views: 4153

Answers (2)

e_i_pi
e_i_pi

Reputation: 4820

For the first column, you can use the Split function and take the first element:

=IIf(
  Split(Fields!myColumn.Value, ",").Length > 1,
  Split(Fields!myColumn.Value, ",").GetValue(0),
  Fields!myColumn.Value
)

For the second column, you can use the Right function to grab the remaining characters:

=IIf(
  Split(Fields!myColumn.Value, ",").Length > 1,
  Right(
    Fields!myColumn.Value,
    LEN(Fields!myColumn.Value) - LEN(Split(Fields!myColumn.Value, ",").GetValue(0)) - 1
  ),
  ""
)

The LEN() function is for BIDS. Use LENGTH() instead if you're in Report Builder.

Upvotes: 1

Nathan Fisher
Nathan Fisher

Reputation: 7941

I agree with the comment from @pedram, but assuming you go down the path of a report, I would be using the InStr function with the Left Function

so you have something like the following function in a column/cell of a table

=Left(Name, InStr(Name, ","))

The InStr function finds the first instance of the comma and reports is position to the Left function

Now this assumes that the the comma is the delimiter.

Upvotes: 0

Related Questions