0

I have a column with values

Name
-----------
John Smith
Lee Tom
Farm Tom Ville
Jack Midu mark LLC

is there any query in sql server that can create dynamic columns for each word.

Sample

I have attached an image with required output. I have almost 1.5 million records of data.

GuidoG
  • 11,359
  • 6
  • 44
  • 79
manish lingamallu
  • 11
  • 1
  • 4
    Are you using MySQL or MS SQL Server? – jarlh Feb 07 '18 at 15:36
  • possible duplicate of https://stackoverflow.com/questions/19885076/how-to-split-string-and-insert-values-into-table-in-sql-server – SAS Feb 07 '18 at 15:47
  • Possible duplicate of [How to split string and insert values into table in SQL Server](https://stackoverflow.com/questions/19885076/how-to-split-string-and-insert-values-into-table-in-sql-server) – SAS Feb 07 '18 at 15:48
  • i am using ms sql server – manish lingamallu Feb 07 '18 at 15:50

1 Answers1

1

Please check following SQL query

;with cte as (
    select
        [name],
        id,
        val
    from names
    cross apply dbo.split([name],' ') 
), mydata as (
select
    [name],
    case when id = 1 then val end as name1,
    case when id = 2 then val end as name2,
    case when id = 3 then val end as name3,
    case when id = 4 then val end as name4,
    case when id = 5 then val end as name5
from cte
)
select
    [name],
    max(name1) name1,
    max(name2) name2,
    max(name3) name3,
    max(name4) name4,
    max(name5) name5
from mydata
group by [name]

I used a user-defined SQL split function, you can find the source codes in referred document Even though you could use SQL STRING_SPLIT function introduced with SQL Server 2016, since it does not return the order like the referred split function it will not be useful in the above pivot-like query

To visualize the output please check following result set

enter image description here

Eralper
  • 6,461
  • 2
  • 21
  • 27
  • I think the omission of the position by MS is a HUGE miss. I would suggest checking out this splitter. http://www.sqlservercentral.com/articles/Tally+Table/72993/ It returns the position but is an inline table valued function instead of the multi statement one at the link you shared. It will be quite a bit faster. – Sean Lange Feb 07 '18 at 15:59
  • Yes I know, the classic solution is still the fastest one against all those fancy solutions (CTEs, XML, CLR, etc) – Eralper Feb 07 '18 at 16:24