Split a field (space delimiter) into separate columns via sql

[ciarra41]

It combines all the numers in one unique jobnumber and separate columns per unique orderNM

OLD
jobNumbers orderNM
78506766 0850 0853 2542 9907
78506766 0850 0853 2592
78506766 0851 0853 2542 4801 9907
78506766 0851 0853 2542 9907
78506766 0851 0853 2592

End Result
jobNumbers orderNm1 orderNm2 orderNm3 orderNm4 orderNm5 orderNm6
78506766 0850 0851 0853 2542 4801 9907


select jobNumbers,([1])as orderNm1,[2]as orderNm2,[3]as orderNm2,[4]as orderNm4,[5]as orderNm5,[6]as orderNm6
FROM (select jobNumbers, ordersNUM,
ROW_NUMBER() OVER(
PARTITION BY jobNumbers ORDER BY ordersNUM) as rownum
FROM (SELECT DISTINCT jobNumbers, ordersNUM
FROM orderHistory) AS D1) AS D2
PIVOT(MAX(ordersNUM)
FOR rownum IN([1],[2],[3],[4],[5],[6])) AS P

I've tried this but I can't get to to only put in the single value per column and unique.

 

[drlex]

From the example supplied, I'd tackle this by unpivoting the source data and with the result set, pivoting a distinct view.

If you haven't discovered Brad Schultz's SQL server blog, it would be worth checking this link for unpivoting:

http://bradsruminations.blogspot.com/2009/10/un-making-list-or-shredding-of-evidence.html

It includes a link back to his article on pivoting.

soi là, soi carré

 

[markros]

You need to first split the values using any of the splitting functions available and then pivot the result, e.g.

DECLARE @Jobs TABLE (JobNumber INT,        orderNM VARCHAR(100))
INSERT INTO @Jobs 
VALUES 
(78506766,	'0850 0853 2542 9907'),
(78506766,	'0850 0853 2592'),
(78506766,	'0851 0853 2542 4801 9907'),
(78506766,	'0851 0853 2542 9907'),
(78506766,	'0851 0853 2592')

;WITH cte AS (SELECT J.JobNumber, F.Value, 
'OrderNm' + LTRIM(DENSE_RANK() OVER (PARTITION BY JobNumber ORDER BY F.Value)) AS Rn  FROM @Jobs J
CROSS APPLY dbo.fnSplit(J.orderNM,' ') F)

SELECT * FROM cte PIVOT (MAX(VALUE) 
FOR Rn IN ([OrderNm1],[OrderNm2], [OrderNm3],[OrderNm4],[OrderNm5],[OrderNm6],[OrderNm7])) pvt

PluralSight Learning Library