Recently, someone asked me a question. They were storing comma separated values in a column of the table. This table was getting populated from some source via SSIS package. Here is the simplified version of the problem, I was presented with.
  1. DECLARE @t TABLE
  2. (
  3. UserID INT,
  4. Certs VARCHAR(8000)
  5. )
  6. INSERT @t VALUES (1,'B.E.,MCA, MCDBA, PGDCA'), (2,'M.Com.,B.Sc.'), (3,'M.Sc.,M.Tech.')
With the data given above, they want it to be split into individual values and search for the possible solutions. Here is the solution query, which I came up with
  1. SELECT UserID,
  2. LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
  3. FROM
  4. (
  5. SELECT UserID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
  6. FROM @t
  7. )t
  8. CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)