I want to data row to column refer detail

Darshan 2015-02-06 10:51:23

Input

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
Aaron Bertrand 2015-02-10 19:18:01
Well, first, I would highly recommend 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.

dwaincsql 2015-02-11 02:54:12
Not that I would do it this way, but how about this (just for fun)!

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;