April 21, 2015 at 3:05 am
Hi ,
I am having staging table with separted by '¯'.
I want to split the data with given number .
i have given 31 means my main table have 31 column.
it should handle the less or more column.
declare @TempTable as Table (Id int identity, sampleData nvarchar(500))
insert into @TempTable (sampleData)
select 'B¯080623719¯¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯Y¯Y¯ ¯' union all
select 'B¯106618392¯¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯'
select
sampleData
, transformed = [dbo].[fnGetFirstOcurrences](sampleData,'¯',31)
from @TempTable
my output should be
select col01,col02,col03,col04,col05,col06,col07,col08,col09,col10,col011,col012,col013,col014,col015,col016,col017,col018,col019,col020,col021,col022,col023,col024,col025,col026,col027,col028,col029,col030,col031 from @TempTable
regards,
Vipin jha
April 21, 2015 at 7:08 am
vipin_jha123 (4/21/2015)
Hi ,I am having staging table with separted by '¯'.
I want to split the data with given number .
i have given 31 means my main table have 31 column.
it should handle the less or more column.
declare @TempTable as Table (Id int identity, sampleData nvarchar(500))
insert into @TempTable (sampleData)
select 'B¯080623719¯¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯Y¯Y¯ ¯' union all
select 'B¯106618392¯¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯'
select
sampleData
, transformed = [dbo].[fnGetFirstOcurrences](sampleData,'¯',31)
from @TempTable
my output should be
select col01,col02,col03,col04,col05,col06,col07,col08,col09,col10,col011,col012,col013,col014,col015,col016,col017,col018,col019,col020,col021,col022,col023,col024,col025,col026,col027,col028,col029,col030,col031 from @TempTable
regards,
Vipin jha
It is very difficult to figure out what you are trying to do. What is the actual question here? At first I thought it was how to split a delimited string but now I am not so sure. If the question is about splitting strings see the link in my signature about that topic. If it is something please try to explain what you are trying to do more clearly.
_______________________________________________________________
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/
April 21, 2015 at 7:36 am
Split + take first given number of values + add nulls if splitted values set is shorter then requied length + PIVOT ?
April 21, 2015 at 7:45 am
serg-52 (4/21/2015)
Split + take first given number of values + add nulls if splitted values set is shorter then requied length + PIVOT ?
I am sure this is crystal clear in your head but it is getting lost in translation. It sounds like you need a combination of the string splitter and a dynamic cross tab that utilizes a tally table. Take a look at the links in my signature about cross tabs to figure out how to turn the parsed string into columns.
I am rather swamped right now with work but this sounds like a super fun challenge. I will try to work something up for this later today if I can find some time.
_______________________________________________________________
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/
April 21, 2015 at 8:32 am
I'm just trying to figure out what OP needs to get as a result. My take, he needs
- get a row set by splitting an initial row,
- delete or add some null rows so that it's length was equal to parameter (31 in his post),
- pivot or crosstab the result set to 31 columns as specified by parameter.
So OP may need slightly modified Jeff Moden's DelimitedSplit8K() TVF and then crosstab it's output as described in his other articles. As a number of columns is parameter, crosstab should be generated dynamically.
See links in Sean Lange's signature. 🙂
April 21, 2015 at 9:47 am
May be something like
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tsample]') AND type in (N'U'))
DROP TABLE [dbo].[tsample]
GO
create table tsample (
id int identity(1,1)
,val varchar(8000));
insert tsample values
('B_080623719__ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _Y_ _ _ _ _Y_Y_ _' )
,('B_106618392__ _ _ _Y_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _');
declare @lp int = 31;
declare @delim char(1) = '_';
declare @sql varchar(max) =
'select id '
+(select ', max(case n when ' + c.n +' then Item end)'+'as col'+ c.n
from [dbo].[DelimitedSplit8KFixedNbr] ('','',@lp)
cross apply (select n=right(cast(100+n as char(3)),2)) c
for xml path(''))
+ '
from tsample
cross apply
[dbo].[DelimitedSplit8KFixedNbr] (val,'''+@delim+''','+cast(@lp as varchar(5))+ ')
group by id';
exec (@sql);
Here DelimitedSplit8KFixedNbr() is a slightly modified DelimitedSplit8K() which accepts an extra parameter = number of rows to be returned. [DelimitedSplit8KFixedNbr] ('','',@lp) produces just @lp enumerated nulls and may be replaced with inline tally for better perfomance of query generation.
April 21, 2015 at 9:51 am
serg-52 (4/21/2015)
May be something like
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tsample]') AND type in (N'U'))DROP TABLE [dbo].[tsample]
GO
create table tsample (
id int identity(1,1)
,val varchar(8000));
insert tsample values
('B_080623719__ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _Y_ _ _ _ _Y_Y_ _' )
,('B_106618392__ _ _ _Y_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _');
declare @lp int = 31;
declare @delim char(1) = '_';
declare @sql varchar(max) =
'select id '
+(select ', max(case n when ' + c.n +' then Item end)'+'as col'+ c.n
from [dbo].[DelimitedSplit8KFixedNbr] ('','',@lp)
cross apply (select n=right(cast(100+n as char(3)),2)) c
for xml path(''))
+ '
from tsample
cross apply
[dbo].[DelimitedSplit8KFixedNbr] (val,'''+@delim+''','+cast(@lp as varchar(5))+ ')
group by id';
exec (@sql);
Here DelimitedSplit8KFixedNbr() is a slightly modified DelimitedSplit8K() which accepts an extra parameter = number of rows to be returned. [DelimitedSplit8KFixedNbr] ('','',@lp) produces just @lp enumerated nulls and may be replaced with inline tally for better perfomance of query generation.
Can you post your function too?
_______________________________________________________________
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/
April 21, 2015 at 2:50 pm
Yes, certainly.
-- based on http://www.sqlservercentral.com/articles/Tally+Table/72993/
DROP FUNCTION [dbo].[DelimitedSplit8KFixedNbr]
go
CREATE FUNCTION [dbo].[DelimitedSplit8KFixedNbr]
(@pString VARCHAR(8000), @pDelimiter CHAR(1)
, @cnt int -- number of items >=1
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== serves both cteStart and final SELECT
SELECT TOP (CASE WHEN ISNULL(DATALENGTH(@pString),0) > @cnt THEN ISNULL(DATALENGTH(@pString),0) ELSE @cnt END
) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== TOP() ajusted to get no more then @cnt rows
SELECT 1 UNION ALL
SELECT TOP (@cnt-1) t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
),
splitted(ItemNumber,Item ) AS(
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
)
-- select exactly @cnt items
SELECT TOP (@cnt) ct.N, s.Item
FROM cteTally ct
LEFT JOIN splitted s ON s.ItemNumber = ct.N
;
April 22, 2015 at 1:48 pm
Your function is missing the ORDER BY at the end. It might seem to return the correct results but it might fail at any time.
April 22, 2015 at 3:18 pm
Luis Cazares (4/22/2015)
Your function is missing the ORDER BY at the end. It might seem to return the correct results but it might fail at any time.
Thank you, Luis. Then i think two ORDER BY should be added, for every SELECT TOP().. FROM cteTally, the second in cteStart(N1).
April 22, 2015 at 5:12 pm
serg-52 (4/22/2015)
Luis Cazares (4/22/2015)
Your function is missing the ORDER BY at the end. It might seem to return the correct results but it might fail at any time.Thank you, Luis. Then i think two ORDER BY should be added, for every SELECT TOP().. FROM cteTally, the second in cteStart(N1).
Not really, the first top doesn't need and ORDER BY because it's just selecting rows and adding the numbers later.
April 23, 2015 at 2:24 am
Luis Cazares (4/22/2015)
Not really, the first top doesn't need and ORDER BY because it's just selecting rows and adding the numbers later.
No, i mean not the first but the second one
cteStart(N1) AS (--==== TOP() ajusted to get no more then @cnt rows
SELECT 1 UNION ALL
SELECT TOP (@cnt-1) t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
ORDER BY t.N
)
Without ORDER BY optimizer some day may decide to return arbitrary set of(@cnt-1) rows from cteTally . While it must be exactly first rows.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply