1

I have data saved as 1¬2¬4¬5¬7 and I want to split them to first response = 1, second response = 2, third response = 4, forth response = 5 and fifth response = 7. They are in the column as varchar(max) and need to report on these. Is there a way to either to split them into 5 different rows or in a ranked list? Not all the columns have 5 parts, some have more and some less so it would be good if the answer was flexible for that.

I'm using SSRS 2008R2 and SQL Server 2008R2

Thank you in advance

user3774310
  • 31
  • 5

1 Answers1

2
declare @Testdata table (  Data varchar(max))
insert @Testdata select   '1¬2¬4¬5¬7'

;with tmp(DataItem, Data) as (
select   LEFT(Data, CHARINDEX('¬',Data+'¬')-1),
    STUFF(Data, 1, CHARINDEX('¬',Data+'¬'), '')
from @Testdata
union all
select   LEFT(Data, CHARINDEX('¬',Data+'¬')-1),
    STUFF(Data, 1, CHARINDEX('¬',Data+'¬'), '')
from tmp
where Data > ''
)

select * from tmp
Tanner
  • 22,205
  • 9
  • 65
  • 83
mohan111
  • 8,633
  • 4
  • 28
  • 55
  • If I wanted to add another column to this query, where would I put it? For example if I want to insert a select statement into @Testdata and bring back a questioned with it? – user3774310 Jun 25 '14 at 14:08