-1

How to split the data in a column with comma separation?

I am attaching the images for my Input and my expecting output.

The following is the input:

enter link description here

The following is my expected output:

enter link description here

Please help me how I will get my required output with a SQL Server query

Thanks in advance, Phani Kumar.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Phani
  • 93
  • 1
  • 8
  • possible duplicate of http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns – Ameya Deshpande Feb 11 '15 at 10:22
  • It is a duplicate but not of that one. That one splts into columns. OP wants to split into rows. This one might suit better: http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco – Nick.Mc Feb 11 '15 at 10:26
  • 1
    Don't store data like comma separated items is the answer! As you have noticed it will only cause you problems!!! – jarlh Feb 11 '15 at 10:26
  • writing links in your question it not a good way to present data. Naming doesn't make it better. – t-clausen.dk Feb 11 '15 at 10:33

1 Answers1

0
declare @t Table (ID INT,Groupname VARCHAR(10),Pid  VARCHAR(20))

insert into @t (ID,Groupname,Pid)values (1,'xxxx','123,568,562,25')
insert into @t (ID,Groupname,Pid)values (2,'yyyy','2,356,321')
insert into @t (ID,Groupname,Pid)values (3,'zzzz','7,898,569')
insert into @t (ID,Groupname,Pid)values (4,'sss','12345')

SELECT ID,Groupname,
PARSENAME(REPLACE(Split.a.value('.', 'VARCHAR(100)'),'-','.'),1) 'Values' 
FROM  
(
     SELECT ID,Groupname,
     CAST ('<M>' + REPLACE([Pid], ',', '</M><M>') + '</M>' AS XML) AS Data 
     FROM @t     
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)
mohan111
  • 8,633
  • 4
  • 28
  • 55