bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-22 : 00:51:37
|
[code]--Dynamic queryCREATE TABLE #test(id int, data varchar(250))INSERT INTO #test SELECT 1, '1540 6 2148 1253 1568' union allSELECT 2, '1220 5 2111 235' union allSELECT 3, '1234 5 5212 1232 1444 2358'SELECT * FROM #testDECLARE @pivot varchar(8000)DECLARE @select varchar(8000)SELECT @pivot=coalesce(@pivot+',','')+'[col'+cast(number+1 as varchar(10))+']'FROM master..spt_values where type='p' and number<=(SELECT max(len(data)-len(replace(data,' ',''))) FROM #test)SELECT @select=' select p.* from ( select id,substring(data, start+2, endPos-Start-2) as token, ''col''+cast(row_number() over(partition by id order by start) as varchar(10)) as n from ( select id, data, n as start, charindex('' '',data,n+2) endPos from (select number as n from master..spt_values where type=''p'') num cross join ( select id, '' '' + data +'' '' as data from #test ) m where n < len(data)-1 and substring(data,n+1,1) = '' '') as data ) pvt Pivot ( max(token)for n in ('+@pivot+'))p' PRINT @selectEXEC(@select)DROP TABLE #test--------------------------------------------------------------Method2: This is based on user-defined function [dbo].[CustomSplit] If the Space separated columns maximum count is known, then you can use the following CREATE TABLE #test(id int, data varchar(100))INSERT INTO #test SELECT 1, '1540 6 2148 1253 1568' union allSELECT 2, '1220 5 2111 235' union allSELECT 3, '1234 5 5212 1232 1444 2358'SELECT * FROM #testSELECT * FROM( SELECT Id, n As ItemNumber, s AS Item FROM #test CROSS APPLY dbo.CustomSplit (' ',[data]) dsk -- CustomSplit)s PIVOT(MAX (Item) FOR ItemNumber IN ([1],[2],[3],[4],[5],[6],[7],,[9],[10], [11],[12],[13],[14],[15],[16],[17],[18],[19],[20], [21],[22],[23],[24],[25],[26],[27],[28],[29],[30], [31],[32],[33],[34],[35],[36],[37],[38],[39],[40], [41],[42],[43],[44],[45],[46],[47],[48],[49],[50]))PDROP TABLE #test;CREATE FUNCTION [dbo].[CustomSplit] (@sep char(1), @s varchar(512))RETURNS tableASRETURN ( WITH Pieces(n, start, stop) AS ( SELECT 1, 1, CHARINDEX(@sep, @s) UNION ALL SELECT n + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) FROM Pieces WHERE stop > 0 ) SELECT n, SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s FROM Pieces[/code]--Chandu |
|
|