# I want to data row to column refer detail

Chart Data 0 123,234,899 1 789,9921 2 333,1222,344

Output

1 2 3 --- ---- ---- 123 789 333 234 9921 1222 899 - 344

*not*storing your data that way – if those are separate pieces of information, they really should be stored separately in the first place, and unless there's a good reason to store them in a way that is different than the way they need to be queried, you should try to store them in a more output-friendly way.

Assuming that you can't fix the design or store the data differently, and that there is only ever one row per Chart value, first let's create a split function that will help break the comma-separated strings apart. This function will work on strings up to about 4,000 characters (exact lengths will depend on SQL Server version and how many columns exist in your database).

CREATE FUNCTION dbo.SplitStrings ( @List NVARCHAR(MAX), @Delimiter NVARCHAR(255) ) RETURNS TABLE AS RETURN (SELECT [Index] = ROW_NUMBER() OVER (ORDER BY Number), Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number))) FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_columns) AS n(Number) WHERE Number <= CONVERT(INT, LEN(@List)) AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter) AS y);

If you need to support longer strings than this, you can use a Numbers table in place of sys.all_columns, or you can make that a CROSS JOIN of, say, sys.all_objects onto itself.

Next, let's assume you have this data:

CREATE TABLE dbo.source(Chart TINYINT PRIMARY KEY, [Data] NVARCHAR(255)); INSERT dbo.source(Chart, [Data]) VALUES (0,N'123,234,899'),(1,N'789,9921'),(2,N'333,1222,344');

The following query will get the result set you're after:

;WITH x AS ( SELECT [Chart], f.[index], f.Item FROM dbo.source AS s CROSS APPLY dbo.SplitStrings(s.[Data], N',') AS f ) SELECT [1] = COALESCE([0],N'-'), [2] = COALESCE([1],N'-'), [3] = COALESCE([2],N'-') FROM ( SELECT [index],[0],[1],[2] FROM x PIVOT (MAX(Item) FOR Chart IN ([0],[1],[2])) AS p ) AS y;

Now, that assumes that you know that you will only ever have three charts. You can construct the pivot query dynamically so that it can handle any number of charts and any number of data points within those charts. Let's insert two more rows into our source table (and note that the Chart values don't have to be contiguous, but you will end up with "empty" columns for any gaps):

INSERT dbo.source(Chart, [Data]) VALUES (4,N'56,67,78,89'),(6,N'0,1,2,3,4,5,6,7,8,9,10,11,12');

Now we can write a slightly more convoluted query to generate a dynamic version of the PIVOT above:

DECLARE @sql NVARCHAR(MAX), @|/4/>columns NVARCHAR(MAX) = N'', @coalesces NVARCHAR(MAX) = N'', @c INT = (SELECT MAX(Chart)+1 FROM dbo.source); ;WITH n(rn) AS ( SELECT TOP (@c) ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects ORDER BY [object_id] ) SELECT @|/4/>columns += STUFF((SELECT N',' + QUOTENAME(rn-1) FROM n ORDER BY rn FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'),1,1,N''), @coalesces += STUFF((SELECT N',' + QUOTENAME(rn) + N' = COALESCE(' + QUOTENAME(rn-1) + ',N''-'')' FROM n ORDER BY rn FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'),1,1,N''); SET @sql = N' ;WITH x AS (SELECT [Chart], f.[index], f.Item FROM dbo.source AS s CROSS APPLY dbo.SplitStrings(s.[Data], N'','') AS f ) SELECT ' + @coalesces + N' FROM ( SELECT [index],' + @|/4/>columns + N' FROM x PIVOT (MAX(Item) FOR Chart IN (' + @|/4/>columns + N')) AS p ) AS y; '; PRINT @sql; EXEC sys.sp_executesql @sql;

If your data set is large, it's quite likely that this query won't be "fast." To be honest, I don't know that there *is* a fast way to break apart and pivot long strings like this.

CREATE TABLE #Source ( Chart INT ,[Data] NVARCHAR(MAX) ); INSERT #source(Chart, [Data]) VALUES (0,N'123,234,899'),(1,N'789,9921'),(2,N'333,1222,344'),(3,N''); DECLARE @SQL NVARCHAR(MAX) = N' WITH rCTE AS ( SELECT Chart, [Data], n=1 ,l=CASE CHARINDEX('','', [Data]) WHEN 0 THEN ''-'' ELSE LEFT([Data], CHARINDEX('','', [Data])-1) END ,r=CASE CHARINDEX('','', [Data]) WHEN 0 THEN ''-'' ELSE SUBSTRING([Data], CHARINDEX('','', [Data])+1, LEN([Data])) END FROM #Source UNION ALL SELECT Chart, [Data], n+1 ,l=LEFT(r, CHARINDEX('','', r)-1) ,r=SUBSTRING(r, CHARINDEX('','', r)+1, LEN(r)) FROM rCTE WHERE CHARINDEX('','', r) > 0 UNION ALL SELECT Chart, [Data], n+1 ,l=r ,r=NULL FROM rCTE WHERE CHARINDEX('','', r) = 0 ) SELECT ' + STUFF( ( SELECT CHAR(10) + ',' + QUOTENAME(Chart + 1) + '= COALESCE(MAX(CASE Chart WHEN ' + CAST(Chart AS NVARCHAR(5)) + N' THEN l END), ''-'')' FROM #Source ORDER BY Chart FOR XML PATH('') ), 1, 2, '') + CHAR(10) + N'FROM rCTE a GROUP BY n ORDER BY n OPTION (MAXRECURSION 0);'; EXEC sp_executesql @SQL; GO DROP TABLE #source;