Row into Multiple Column

Question

Row into Multiple Column

sqlenthu_11

SSC Eights!

Points: 800
More actions
September 1, 2015 at 11:02 pm

#311273

Need help !!!
Create table control_Total ( Filename varchar(1000) )
insert into control_Total values('PCH123_TLNX.account.TUED.20150831.txt Bytes:645 Records:4')
insert into control_Total values('PCH123_TLNY.account.TWED.20150831.txt Bytes:1920 Records:12')
how can I get output like this :
FileName RecordCountBytes
PCH123_TLNX.account.TUED.20150831.txt4645
PCH123_TLNY.account.TWED.20150831.txt121920
Thanks.
nitin

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

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

pietlinden SSC Guru Points: 63425 More actions · Answer 1

Nitin,

The short answer to your question is to use DelimitedSplit8K[/url] to split the text strings into different columns and then deal with those.

This was my first try splitting (just so you can see how to use the function... but you have to read the article and follow the instructions to create the function in your own database first...)

SELECT x.theFileName

, MIN(CASE WHEN x.Item LIKE 'Bytes%' THEN RIGHT(x.Item,LEN(x.Item)-CHARINDEX(':',x.Item,1)) END) AS Bytes

, MIN(CASE WHEN x.Item LIKE 'Records%' THEN RIGHT(x.Item,LEN(x.Item)-CHARINDEX(':',x.Item,1)) END) AS Records

FROM

( SELECT test.[FileName] AS theFileName

, split.ItemNumber

, split.Item

FROM control_Total test

CROSS APPLY MyDatabase.dbo.DelimitedSplit8k(test.[FileName],' ') split) x

WHERE x.Item LIKE 'Bytes%' OR x.Item LIKE 'Records%'

GROUP BY x.theFileName;

I had to use MIN/GROUP BY because the DelimitedSplit8K function returns a table, and populates the table with the individual values that get split out of the input, in this case theFileName (I changed it because FileName is a reserved word in T-SQL.)

Hope this helps!

Pieter

sqlenthu_11 SSC Eights! Points: 800 More actions · Answer 2

sqlenthu_11

SSC Eights!

Points: 800

September 2, 2015 at 6:36 am

#1823949

Thanks man. It helped.

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

The DelimitedSplit8K is a bit overkill for this. Here is another option using PARSENAME.

select replace(parsename(replace(REPLACE(Filename, '.', ''), ':', '.'), 2), ' Records', '') as Bytes

, parsename(replace(REPLACE(Filename, '.', '^'), ':', '.'), 1) as Records

from control_Total

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Yet another option 😀

SELECT LEFT( Filename, Start.Bytes - 1) AS theFilename,

SUBSTRING(Filename, Start.Bytes + 7, Start.Records - Start.Bytes - 7) AS Bytes,

SUBSTRING(Filename, Start.Records + 9, 100) AS Records

FROM control_Total

CROSS APPLY (SELECT CHARINDEX(' Bytes:', Filename),

CHARINDEX(' Records:', Filename))Start(Bytes,Records)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

pietlinden SSC Guru Points: 63425 More actions · Answer 5

Interesting... Thanks Sean and Luis for the gentle "schooling". I'll have to add that to my SSC database (the stuff I brazenly steal from here... hey, gotta learn somehow!)

Maybe I have the "when all you [know] is a hammer..." problem.

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

pietlinden (9/2/2015)
Interesting... Thanks Sean and Luis for the gentle "schooling". I'll have to add that to my SSC database (the stuff I brazenly steal from here... hey, gotta learn somehow!)
Maybe I have the "when all you [know] is a hammer..." problem.

The delimited splitter is kind of a default "go to" for splits. No problem there at all. We were just presenting a couple of alternatives. And having that splitter in your arsenal is critical for a number of things. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 7

select

substring(Filename,1,charindex('Bytes',Filename) -1) as Filename,

replace(substring(Filename,charindex('Bytes',Filename),charindex('Record',Filename) -charindex('Bytes',Filename)),'Bytes:','') as Bytes,

replace(substring(Filename,charindex('Records',Filename),len(Filename)),'Records:','') as 'Records'

from control_Total