sql server 2008 - Get a value in a single cell into multiple rows using SSRS -


There is a value in the format below a particular field in the SQL table

value 11, value 12, Value 13

Value 21, Value 22, Value 23

...

...

Using SSRS Each line given in each row must be taken in different lines of text. For example, I would get 2 rows in the report for the above figures. Is there a way to use a reporting project in VS or Report Builder?

Thanks in advance.

[id] int

Description: Insert the values ​​into the value (1, 'value11, value12,', 'value', and ' Value13

Value21, value22, value23 ')

Then there is a carriage return caharacter from the included above for the details column.

So my requirement is that when I retrieve the data, I should go to the format below.

ID, Description

1, value 11, value 12, value 13

1, value 21, value 22, value 23

<> for reporting data you can use this to select data . 

  Select t1.id, from t2.splittedDescriptions (select tblTest .id, cast ('& lt; line & gt;'. + REPLACE (tblTest [description], CHAR (13) + CHAR (10), as' xmlrow from tblTest ') as' xml'); & lt; / line & gt; & lt; line & gt; ') T1 cross applied (select xmlTable.splittedRow.value (as '.', 'VARCHAR (MAX)') as xmlTable (SplittedRow t1.xmlRow.nodes ('/ line') splittedDescriptions as in the form) T2 < / Code>

uses XML and when it gets CRLF to split your details.

You can just modify SELECT if you need to work with a single CRLF , if you double CRLF Are there. Example - Input data: 

  INSERT tblTest ([id], [description]) value (1, 'val11, val12, val13' + CHAR (13) + CHAR (10) 'val21, Val22, val23 '+) Enter the value in tblTest ([id], [insert check (), value (2,' val31, val32, val33 ') into tblTest ([id], [description]) values ​​(3,' val41, Val42, val43 '+ CHAR (13) + CHAR (10) +' val51, val52, val53 '+ char (13) + char (10) +' val61, val62, val63 ')
< P> example - output: 

  id splittedDescriptions ----------- -------------------- 1 Val11, val12, val13 1 val21, val22, val23 2 val31, val32, val33 3 val41, val42, val43 3 val51, val52, val53 3 val61, val62, val63

Comments

Post a Comment