CSV To Table

CSV To Table

CREATE Procedure [dbo].[CSVToTable]
@CSV NVARCHAR(MAX),
@firstRowHeadings int = 1,
@Delimiter char(1) = ',',
@LineTerminator char(1) = null,
@DoRowOrderColumn int = 0
as
declare @MyHierarchy hierarchy
insert into @MyHierarchy
  Select * from parseCSV(@CSV, @firstRowHeadings,@Delimiter,@LineTerminator)

Declare @Command NVarchar(2000)
if @DoRowOrderColumn  = 0
  Select @command= 'Select'+stuff((Select line from (
  Select distinct ',
    MAX( case when sequenceNo='+convert(Varchar(10),sequenceNo)+' then StringValue else '''' end) as ['+name+']'
  from @MyHierarchy  where object_ID is null union all
  select '
  FROM @TheHierarchy WHERE object_ID is null
  GROUP BY parent_ID ORDER BY parent_ID' )f(line)
  FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')
else
  Select @command= (Select line+'
  ' from (
  Select 'Select parent_ID as [Row]' union all
  Select  distinct ',  MAX( case when sequenceNo='+convert(Varchar(10),sequenceNo)+' then StringValue else '''' end) as ['+name+']'
  from @MyHierarchy where object_ID is null union all
  select 'from @TheHierarchy where object_ID is null
  group by parent_ID order by parent_ID' )f(line)
  FOR XML PATH(''), TYPE).value('.', 'varchar(max)')

EXEC sp_executesql @Command,
      N'@TheHierarchy hierarchy READONLY',
      @TheHierarchy = @MyHierarchy