[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Syntax question

Posted on 2009-02-24
25
Medium Priority
?
230 Views
Last Modified: 2012-05-06
I have posted something similiar to this before. I have 1 row with an AccountID, Code and QTY. The accountid only has one value but the code and qty columns contain x number of values ';' delimited. I need the accountid, codes and qty columns parsed out for each one of the codes and qty columns into there own rows. Is there an easier way? I am trying to stay away from cursors but whatever will work I am willing to use. Please help!!
0
Comment
Question by:healthcheckinc
  • 13
  • 9
  • 2
24 Comments
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23721962
Easiest way would be to split out the code and QTY so it's a 1:1:1 relationship.
Table grows, but tables do that.

IF you wanted to keep all the same:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24034874.html
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23721987
What DB Engine and Version?

If it's SQL Server 2005 you can use a UDF to split the records.  Just provide your table names and your existing Query, then I can help you out.
0
 

Author Comment

by:healthcheckinc
ID: 23722066
SQL Server 2005 v. 9.000.1399
The code below ran for 15 minutes. Ive used it to parse 1 column, in another scenario, so I tried to use it again but with an extra column 'QTY'. I think Im close but it was taking too long...And GED325 is right I want them all 1:1:1...

Existing code:
insert into GlendaleTest
      select PatAccount, Codes,Qty from GlendaleTemp;
 
     ;WITH CTE (ID, Code, Remain, Level,Qty)
      AS
      (
      -- Anchor member definition
      SELECT PatAccount, Code = Codes, Remain = Codes, Level = 0,Qty
      FROM GlendaleTest
      UNION ALL
      -- Recursive member definition
      SELECT ID,
      Code = left(Remain, isnull(position_of_space, remain_length)),
      Remain = right(Remain, isnull(remain_length - position_of_space, 0)), Level = 1,Qty
            FROM
            (  SELECT
      position_of_space = nullif(PatIndex('%;%', Remain), 0),
      remain_length = len(Remain),
      CTE.ID, CTE.Remain,CTE.Qty
      FROM CTE
      INNER JOIN GlendaleTest on GlendaleTest.PatAccount = CTE.ID
      WHERE Remain <> ''
            ) X
      )
      -- Statement that executes the CTE
      SELECT ID, Code,QTY
      INTO GlendaleStage
      FROM CTE
      WHERE Level = 1
      Order by id
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23722100
sql 2005 is easy:

Use the cross apply (given in my first link) to get the apprropriate select statement down.    Would then re-insert to a new table and drop the old.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23722180
What datatype is PatAccount?
0
 

Author Comment

by:healthcheckinc
ID: 23722186
will try...Thanks
0
 

Author Comment

by:healthcheckinc
ID: 23722196
PatAccount varchar(50)
Codes varchar(max)
QTY varchar(max)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23722216
I think the problem with using Cross apply will be relating the 1,2,3,4,5 CODES to 1,2,3,4,5 QTY.
0
 

Author Comment

by:healthcheckinc
ID: 23722247
I was just thinking on how to apply the function to the 2 fields myself? How would the select look? If you guys need data let me know...Thank you both for your help...
0
 

Author Comment

by:healthcheckinc
ID: 23722295
This worked for me to seprate out even more columns that were in the same format but I have had trouble getting it to work

ALTER function [dbo].[fn_Split](@ID int) returns @temp table(ID int,CODE varchar(500),QTY varchar(500),AMT varchar(500),CPTCode varchar(500)) as
begin

declare @Code varchar(5000),@Qty varchar(5000),@Amt varchar(5000),@CPTCode varchar(5000)
declare @SubCode varchar(5000),@SubQty varchar(5000),@SubAmt varchar(5000),@SubCPTCode varchar(5000)
declare @string varchar(5000)
declare @CodeTable table(UniqueID int identity,ID int,CODE varchar(500))
declare @QtyTable table(UniqueID int identity,ID int,QTY varchar(500))
declare @AmtTable table(UniqueID int identity,ID int,AMT varchar(500))
declare @CPTCodeTable table(UniqueID int identity,ID int,CPTCode varchar(500))
select @Code  = RevCodes from SacramentoTrans t1 where PatAccount = @ID
select @Qty  = QTY from SacramentoTrans t1 where PatAccount = @ID
select @Amt  = Amount from SacramentoTrans t1 where PatAccount = @ID
select @CPTCode  = CPTCodes from SacramentoTrans t1 where PatAccount = @ID
set @SubCode = @Code+';'
set @SubQty = @Qty+';'
set @SubAmt = @Amt+';'
set @SubCPTCode = @CPTCode+';'

while @SubCode <> '' and len(@SubCode) > 0 and @SubCode IS NOT NULL
begin
set @string = substring(@SubCode,1,charindex(';',@SubCode)-1)
insert @CodeTable values (@ID,@string)
set @SubCode = substring(@SubCode,charindex(';',@SubCode)+1,len(@SubCode))
end
 
while @SubQty <> '' and len(@SubQty) > 0 and @SubQty IS NOT NULL
begin
set @string = substring(@SubQty,1,charindex(';',@SubQty)-1)
insert @QtyTable values (@ID,@string)
set @SubQty = substring(@SubQty,charindex(';',@SubQty)+1,len(@SubQty))
end
 
while @SubAmt <> '' and len(@SubAmt) > 0 and @SubAmt IS NOT NULL
begin
set @string = substring(@SubAmt,1,charindex(';',@SubAmt)-1)
insert @AmtTable values (@ID,@string)
set @SubAmt = substring(@SubAmt,charindex(';',@SubAmt)+1,len(@SubAmt))
end
 
while @SubCPTCode <> '' and len(@SubCPTCode) > 0 and @SubCPTCode IS NOT NULL
begin
set @string = substring(@SubCPTCode,1,charindex(';',@SubCPTCode)-1)
insert @CPTCodeTable values (@ID,@string)
set @SubCPTCode = substring(@SubCPTCode,charindex(';',@SubCPTCode)+1,len(@SubCPTCode))
end
 
insert into @temp
select coalesce(t5.id,t6.id) ID ,CODE,QTY,AMT,CPTCode
 from (
select coalesce(t3.id,t4.id) ID ,coalesce(t3.UniqueID,t4.UniqueID) UniqueID,CODE,QTY,AMT
  from(
select coalesce(t1.id,t2.id) ID ,coalesce(t1.UniqueID,t2.UniqueID) UniqueID,CODE,QTY
 from @CodeTable t1 full join @QtyTable t2 on t1.ID = t2.ID and t1.UniqueID = t2.UniqueID) t3
 full join @AmtTable t4 on t3.ID = t4.ID and t3.UniqueID = t4.UniqueID) t5
 full join @CPTCodeTable t6 on t5.ID = t6.ID and t5.UniqueID = t6.UniqueID return
end







0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 23722564
Let's try this.

Select PatAccount, qty,code, fn.qty,fn.code
from GlendateTest gt
cross apply [dbo].[fn_DelimitedToTable_Qty_Code] (gt.pataccount) fn

Using this function.
if object_id('[dbo].[fn_DelimitedToTable_Qty_Code]') is not null
     drop function [dbo].[fn_DelimitedToTable_Qty_Code]
go
create function [dbo].[fn_DelimitedToTable_Qty_Code](@PatAccount varchar(50))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,Qty           nvarchar(max)
     ,Code          nvarchar(max)
     )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 07-Oct-2008
*
*************************************************************/
begin
     declare @Delimiter  nchar(1)
          ,@Qty          nvarchar(max)
          ,@Codes        nvarchar(max)
 
     select @Qty = qty, @Codes = Codes
     from GlendaleTest
     where PatAccount = @PatAccount
 
declare @Values2 TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,Qty           nvarchar(max)
     ,Code          nvarchar(max)
     )
 
     set @Delimiter=N'n'
     insert into @Values2 (thePosition,Qty)
		select vn.n, substring(@delimiter + @Qty + @delimiter, vn.n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @Qty + @delimiter, vn.n + datalength(@delimiter)/2) - vn.n - datalength(@delimiter)/2) as string_value
		from	dbo.vw_Nums vn
		where
			vn.n <= (datalength(@delimiter + @Qty + @delimiter)/2) - (datalength(@delimiter)/2)
			and substring(@delimiter + @Qty + @delimiter, vn.n, (datalength(@delimiter)/2)) = @delimiter
 
     insert into @Values2 (thePosition,Codes)
	select vn.n, substring(@delimiter + @Codes + @delimiter, vn.n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @Codes + @delimiter, vn.n + datalength(@delimiter)/2) - vn.n - datalength(@delimiter)/2) as string_value
	from	dbo.vw_Nums vn
	where
		vn.n <= (datalength(@delimiter + @Codes + @delimiter)/2) - (datalength(@delimiter)/2)
		and substring(@delimiter + @Codes + @delimiter, vn.n, (datalength(@delimiter)/2)) = @delimiter
 
     insert into @Values (thePosition,Qty,Code)
     select a.ThePosition, a.Qty,b.Code
     from @Values2 a
       join @Values2 b
         on a.theposition=b.thePosition
         and a.Qty is not null
         and b.Code is not null
return
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
 
Sample Usage:
select * from [dbo].[fn_DelimitedToTable_Qty_Code]('a|%25basdf|%25c|%25d','|%25')
select theValue from [dbo].[fn_DelimitedToTable_Qty_Code]('a','|')
select * from [dbo].[fn_DelimitedToTable_Qty_Code]('a basdf c d',' ')
*/
GO

Open in new window

0
 

Author Comment

by:healthcheckinc
ID: 23723305
Brandon: I ran your query using the function and I got the same data that I had, in the same format. I created the view as well.....
0
 

Author Comment

by:healthcheckinc
ID: 23723733
Hey guys I did it with this....Thanks for all your help...Split the points?

declare @Code varchar(5000),@Qty varchar(5000)
declare @SubCode varchar(5000),@SubQty varchar(5000)
declare @string varchar(5000)
declare @CodeTable table(UniqueID int identity,ID varchar(50),CODE varchar(500))
declare @QtyTable table(UniqueID int identity,ID varchar(50),QTY varchar(500))
select @Code  = Codes from GlendaleTempRev t1 where PatAccount = @ID
select @Qty  = QTY from GlendaleTempRev t1 where PatAccount = @ID
set @SubCode = @Code+';'
set @SubQty = @Qty+';'

while @SubCode <> '' and len(@SubCode) > 0 and @SubCode IS NOT NULL
begin
set @string = substring(@SubCode,1,charindex(';',@SubCode)-1)
insert @CodeTable values (@ID,@string)
set @SubCode = substring(@SubCode,charindex(';',@SubCode)+1,len(@SubCode))
end
 
while @SubQty <> '' and len(@SubQty) > 0 and @SubQty IS NOT NULL
begin
set @string = substring(@SubQty,1,charindex(';',@SubQty)-1)
insert @QtyTable values (@ID,@string)
set @SubQty = substring(@SubQty,charindex(';',@SubQty)+1,len(@SubQty))
end
 
insert into @temp
select coalesce(t1.id,t2.id) ID ,CODE,QTY
 from @CodeTable t1 full join @QtyTable t2 on t1.ID = t2.ID and t1.UniqueID = t2.UniqueID
return
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23723856
Same results, sure.  Because you said what you wanted.  But was it faster?
0
 

Author Comment

by:healthcheckinc
ID: 23723877
I didnt need the same results. I needed the results parsed out. Miscommunication
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23723929
it should have shown you qty and code (your original columns) as well as the parsed qty/code values.  Are you SURE you have the SQL as below?

Select PatAccount, qty,code, fn.qty,fn.code
from GlendateTest gt
cross apply [dbo].[fn_DelimitedToTable_Qty_Code] (gt.pataccount) fn

0
 

Author Comment

by:healthcheckinc
ID: 23723949
Yeah and it showed my table columns and columns using the function exactly the same.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23723982
I found a typo....


set @Delimiter=N'n'

should be:

set @Delimiter=N','
if object_id('[dbo].[fn_DelimitedToTable_Qty_Code]') is not null
     drop function [dbo].[fn_DelimitedToTable_Qty_Code]
go
create function [dbo].[fn_DelimitedToTable_Qty_Code](@PatAccount varchar(50))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,Qty           nvarchar(max)
     ,Code          nvarchar(max)
     )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 07-Oct-2008
*
*************************************************************/
begin
     declare @Delimiter  nchar(1)
          ,@Qty          nvarchar(max)
          ,@Codes        nvarchar(max)
 
     select @Qty = qty, @Codes = Codes
     from GlendaleTest
     where PatAccount = @PatAccount
 
declare @Values2 TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,Qty           nvarchar(max)
     ,Code          nvarchar(max)
     )
 
     set @Delimiter=N','
     insert into @Values2 (thePosition,Qty)
                select vn.n, substring(@delimiter + @Qty + @delimiter, vn.n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @Qty + @delimiter, vn.n + datalength(@delimiter)/2) - vn.n - datalength(@delimiter)/2) as string_value
                from    dbo.vw_Nums vn
                where
                        vn.n <= (datalength(@delimiter + @Qty + @delimiter)/2) - (datalength(@delimiter)/2)
                        and substring(@delimiter + @Qty + @delimiter, vn.n, (datalength(@delimiter)/2)) = @delimiter
 
     insert into @Values2 (thePosition,Codes)
        select vn.n, substring(@delimiter + @Codes + @delimiter, vn.n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @Codes + @delimiter, vn.n + datalength(@delimiter)/2) - vn.n - datalength(@delimiter)/2) as string_value
        from    dbo.vw_Nums vn
        where
                vn.n <= (datalength(@delimiter + @Codes + @delimiter)/2) - (datalength(@delimiter)/2)
                and substring(@delimiter + @Codes + @delimiter, vn.n, (datalength(@delimiter)/2)) = @delimiter
 
     insert into @Values (thePosition,Qty,Code)
     select a.ThePosition, a.Qty,b.Code
     from @Values2 a
       join @Values2 b
         on a.theposition=b.thePosition
         and a.Qty is not null
         and b.Code is not null
return
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
 
Sample Usage:
select * from [dbo].[fn_DelimitedToTable_Qty_Code]('a|%25basdf|%25c|%25d','|%25')
select theValue from [dbo].[fn_DelimitedToTable_Qty_Code]('a','|')
select * from [dbo].[fn_DelimitedToTable_Qty_Code]('a basdf c d',' ')
*/
GO

Open in new window

0
 

Author Comment

by:healthcheckinc
ID: 23724002
No prob man, keep checkin for me because I always got questions and I will throw the points to you for your help..Thanks again..
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23724159
I think you should have it re-opened and accept the updated function with a better grade.  A typo doesn't warrant a "B" and you didn't exactly give me an opportunity to fix it :).
0
 

Author Comment

by:healthcheckinc
ID: 23724183
No problem, I got no problem with that. Just dont know how
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23724204
"request Attention" via the link in your original post.  Just ask for it to be reopened.
0
 

Author Closing Comment

by:healthcheckinc
ID: 31550568
Thanks Brandon
0
 

Author Comment

by:healthcheckinc
ID: 23726514
Thank you!!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question