SQL Query Help

Question

Post reply

SQL Query Help

SQL Server

Old Hand

Points: 377

July 25, 2019 at 5:14 pm

#3666141

Hi All:

Need help with query. I have data in this format:

create table test
(name varchar(1000),
 numbers varchar (100))

 insert into test values ('ABC PQR; XYZ ABC;','312; 435;')
 insert into test values ('SQL; Windows; Unix;','456; 345;')
 insert into test values ('Tom John;','254; 376;')

I want the output to be like this:

Capture

Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

rVadim Hall of Fame Points: 3969 More actions · Answer 1

Look into STRING_SPLIT() function:

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

--Vadim R.

SQL Server Old Hand Points: 377 More actions · Answer 2

I had tried string_function, but that only output single column.

Thanks

Michael L John One Orange Chip Points: 27045 More actions · Answer 3

Does this get you started?

 SELECT * 
  FROM test T
  CROSS APPLY DelimitedSplit8K(T.name, ';') NM
  CROSS APPLY DelimitedSplit8K(T.numbers, ';') NU 
  WHERE NM.ItemNumber = NU.ItemNumber
  AND NM.ItemValue <> ''

Do a search on this site for "DelimitedSplit8k"

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

pietlinden SSC Guru Points: 63436 More actions · Answer 4

thanks for the head start... I was curious, so I finished it.

 SELECT nm.item,
 Name = NU.Item 
  FROM test T
  CROSS APPLY SCRIDB.dbo.DelimitedSplit8K(T.name, ';') NM
  CROSS APPLY SCRIDB.dbo.DelimitedSplit8K(T.numbers, ';') NU 
  WHERE NM.ItemNumber = NU.ItemNumber
  AND NOT (nm.Item ='' AND nu.item ='');

Michael L John One Orange Chip Points: 27045 More actions · Answer 5

One thing. This is not a great design.

I would recommend trying to change the design on these tables. Or, at minimum, create a regular extract of these tables into another set of tables if the design cannot be modified

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

pietlinden SSC Guru Points: 63436 More actions · Answer 6

Amen to that! It's a truly awful design. To the OP - performance of queries against a table like this are going to be painfully slow. You should be able to use DelimitedSplit8K to split up the values in the columns and then insert the results into a permanent table - and index it.