Tuesday, May 27, 2014

Convert Column in Table to Separate Table


declare @test table(ID int, FAQ varchar(35), MODELS varchar(35) );
insert into @test values
(1,'faq1', 'model1'),(2,'faq2', 'model2 model1'),(3,'faq3', 'model3 model2 model1');

SELECT distinct FAQ, S.a.value('.', 'VARCHAR(100)') AS splitVal
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(MODELS, ' ', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM @test) d

CROSS APPLY d.[vals].nodes('/H/r') S(a)


No comments:

Post a Comment