Split comma separated values in a column to multiple columns in SQL...

In this post I am sharing how to split comma separated values in a column to multiple column in SQL.
In this case, we have a table with two columns. One is with the Code and the other column with the seven number values separated by a comma as Numbers

Code           Numbers
A                10,2,55,5,67,11,45
C                1,32,5,15,7,71,35
--                 ---------------------

The expected output is as below

Code          V1  V2  V3  V4  V5  V6  V7
A                10     2   55    5    67  11   45
C                   1   32    5   15     7   71  35


It can be achieved by using the below query.

SELECT Code, [1] AS [V1],[2] AS [V2],[3] AS [V3],[4] AS [V4],[5] AS [V5],[6] AS [V6],[7] AS [V7]
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY [Code] ORDER BY (SELECT  null)) as rn
FROM (
    SELECT [Code], [Numbers]
    FROM [SampleTable]
) as a
CROSS APPLY string_split([Numbers],',')
) as SourceTable
PIVOT
(
    MAX([value])
    FOR rn IN ([1],[2],[3],[4],[5],[6],[7])
)as pivotTable

Hope this will be helpful...

Automating Excel Report Refresh...

The requirement is to refresh an Excel file in the SQL Report Server by running the Macro in the Excel file. In this post, I'll summa...