Loops and building comma delimited strings

The problem:

I have 2 tables, with a one to many relationship - lets say customers, and order items.

Each order record has a field that is meant to be a comma delimited list (they are reference numbers) that is driven by the quantity field. So, say in the order record, an item has a quantity of 3. The reference number will look like this:

1, 2, 3

And if the next order item for that customer has a quantity of 4, the reference number value is

4, 5, 6, 7

And the final item with quantity of 2:

8, 9

Reference numbers can either be auto assigned (and are in my web application) or manually set. If manually set they will NOT be numeric.

In my web application, it is possible for users to return to a customer's order and edit a line item. My problem is when users changes the quantity of an item, and I have to reset the reference numbers.

If the quantity of line item 2 changes from 4 to 3, I need to reset all the values for that, and any other, order item that comes after it:

4, 5, 6 (2nd)
7,8 (3rd with same quantity of 2).

I felt a cursor would be the best way to handle this. But I am having trouble re-assigning my variable to be the next number in the series when the cursor is running.

This is what I have so far. The print lines and hard coded values are for debugging purposes only.

DECLARE @.NumberingType varchar(10)
DECLARE @.TotalSum int
DECLARE @.DoorLineItemID int
DECLARE @.Quantity int
DECLARE @.SeedInt int

SET @.SeedInt = 1

SELECT @.TotalSum = SUM(Quantity) FROM DoorLineItems WHERE UniversalOrderID = 12345

DECLARE UpdateRefCursor CURSOR FOR
SELECT DoorLineItemID, Quantity FROM DoorLineItems WHERE UniversalOrderID = 12345 AND NumberingType = 1

OPEN UpdateRefCursor

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity
DECLARE @.RefNumberLine varchar(1024)
SET @.RefNumberLine = ''

WHILE @.@.FETCH_STATUS = 0
BEGIN

WHILE @.SeedInt <= @.Quantity
BEGIN

SET @.RefNumberLine = @.RefNumberLine + CONVERT(varchar, @.SeedInt, 101) + ', '
SET @.SeedInt = @.SeedInt + 1

END
PRINT @.RefNumberLine

SET @.SeedInt = @.Quantity + @.SeedInt
PRINT 'new seed: ' + CONVERT(varchar, @.SeedInt, 101) + 'Quantity ' + CONVERT(varchar, @.Quantity + @.SeedInt, 101)

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity

END

CLOSE UpdateRefCursor
DEALLOCATE UpdateRefCursor

This returns the same delimited string for X number of items. So I'm getting this:

1,2,3
1,2,3
1,2,3

When I really want the results described above.

What am I doing wrong?

Thanks!

You really need to post a table structure and some data for us to use to try this out. That's a lot of variables with no data to reference to try out.|||solved. Thanks for your input.

LOOPING UPDATE

Hi
I just require a bit of guidance on a SQL query I am writing. I am updating
a table with values, and the first field is a TYPE field, I set this to A
and then populate fields 2-15 with a variety of default updates and values
from other tables, I then do a second insert whreeby I set the same TYPE
field to B and update fields 2-5 and then the unique fields 16-20.
At the moment my second update has two issues;
1. It sets EVERY type to B (although it correctly doubles the amount of
entries in the table)
2. The B entries are appended to the bottom of the table, ideally I want the
table structure to be ABABABAB etc
Any help to a SQL newbie appreciated!> 1. It sets EVERY type to B (although it correctly doubles the amount of
> entries in the table)
Sounds like your WHERE clause may be at fault. Could you post a CREATE TABLE
statement and the UPDATE/INSERT statements so that we can reproduce your
problem.

> 2. The B entries are appended to the bottom of the table, ideally I want
the
> table structure to be ABABABAB etc
Tables have no logical ordering. If you want to see the results in a
particular order then use ORDER BY on a SELECT statement when you query the
table. A clustered index orders data in physical storage but not necessarily
when you query the table.
David Portas
SQL Server MVP
--|||Hi David
I have rewritten the part of the routine with a separate WHERE clause on the
type field at the end of the update and this seems to work so thank you.
I have several more loops to write, I will then try the Order by at the end
of the routine.
Thank you very very much for your quick and informative response, it really
is appreciated.
Steve
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:8ZKdncKsYuPgDrHdRVn-uA@.giganews.com...
> Sounds like your WHERE clause may be at fault. Could you post a CREATE
TABLE
> statement and the UPDATE/INSERT statements so that we can reproduce your
> problem.
>
> the
> Tables have no logical ordering. If you want to see the results in a
> particular order then use ORDER BY on a SELECT statement when you query
the
> table. A clustered index orders data in physical storage but not
necessarily
> when you query the table.
> --
> David Portas
> SQL Server MVP
> --
>sql

LOOPING UPDATE

Hi
I just require a bit of guidance on a SQL query I am writing. I am updating
a table with values, and the first field is a TYPE field, I set this to A
and then populate fields 2-15 with a variety of default updates and values
from other tables, I then do a second insert whreeby I set the same TYPE
field to B and update fields 2-5 and then the unique fields 16-20.
At the moment my second update has two issues;
1. It sets EVERY type to B (although it correctly doubles the amount of
entries in the table)
2. The B entries are appended to the bottom of the table, ideally I want the
table structure to be ABABABAB etc
Any help to a SQL newbie appreciated!> 1. It sets EVERY type to B (although it correctly doubles the amount of
> entries in the table)
Sounds like your WHERE clause may be at fault. Could you post a CREATE TABLE
statement and the UPDATE/INSERT statements so that we can reproduce your
problem.
> 2. The B entries are appended to the bottom of the table, ideally I want
the
> table structure to be ABABABAB etc
Tables have no logical ordering. If you want to see the results in a
particular order then use ORDER BY on a SELECT statement when you query the
table. A clustered index orders data in physical storage but not necessarily
when you query the table.
--
David Portas
SQL Server MVP
--|||Hi David
I have rewritten the part of the routine with a separate WHERE clause on the
type field at the end of the update and this seems to work so thank you.
I have several more loops to write, I will then try the Order by at the end
of the routine.
Thank you very very much for your quick and informative response, it really
is appreciated.
Steve
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:8ZKdncKsYuPgDrHdRVn-uA@.giganews.com...
> > 1. It sets EVERY type to B (although it correctly doubles the amount of
> > entries in the table)
> Sounds like your WHERE clause may be at fault. Could you post a CREATE
TABLE
> statement and the UPDATE/INSERT statements so that we can reproduce your
> problem.
> > 2. The B entries are appended to the bottom of the table, ideally I want
> the
> > table structure to be ABABABAB etc
> Tables have no logical ordering. If you want to see the results in a
> particular order then use ORDER BY on a SELECT statement when you query
the
> table. A clustered index orders data in physical storage but not
necessarily
> when you query the table.
> --
> David Portas
> SQL Server MVP
> --
>

looping through field in table

Dear All

I have a table, and in one of the fields is the following information:
1,2,3,4,5, etc...

How do I 'loop' through this field to remove each comma and put the number into it's own field in another table - so remove the first comma, and put the number 1 into its own field, remove the 2nd comma and put the number 2 into it's own field etc, until all the numbers are in their own fields.

So it ends up being like this:

Col1 Col2 Col3 Col4 Col5 Col n.............
1 2 3 4 5 n...........

There may not be 5 numbers in the field, sometimes more, sometimes less, so i need to be able to tell when there are no more commas and numbers left

Your help is much appreciated

Thanks

GillI have a function for extracting the nth element of a delimited string, if you can use functions try this:

-- =============================================
-- Create inline function
-- =============================================
IF objectproperty(object_id(N'GetStringElement'),'IsS calarFunction') = 1
DROP FUNCTION GetStringElement
GO

CREATE FUNCTION GetStringElement(
@.String varchar(100)
, @.Element int
, @.Seperator char(1) = ',')

RETURNS varchar(100)
AS
begin
declare @.Pass int, @.Index int, @.LastIndex int, @.Return varchar(100)

if (@.String like '%' + replicate(',%',@.Element - 1)) begin
select @.Pass = 1
, @.Index = 1
, @.LastIndex = 0

while (@.Pass <= @.Element) begin
select @.LastIndex = case @.Index when 1 then 0 else @.Index end
, @.Index = charindex(',',@.String,@.Index + 1)
, @.Pass = @.Pass + 1
end

if (@.LastIndex > 0 and @.Index = 0) set @.Index = len(@.String) + 1

set @.Return = substring(@.String,@.LastIndex + 1, @.Index - @.LastIndex - 1)
end

RETURN @.Return
end
GO

if object_id('tempdb..#psy') is not null
drop table #psy

create table #psy(f1 int identity(1,1) not null,f2 varchar(25),col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10),col5 varchar(10),col6 varchar(10))

insert into #psy (f2) values('1,2,3,4,5')
insert into #psy (f2) values('a,b,c')
insert into #psy (f2) values('George,John,Paul,Ringo')

select * From #psy

declare @.pass int, @.ColCount int, @.TSQL varchar(255)
select @.pass = 1
, @.ColCount = 6
while (@.pass <= @.ColCount) begin
select @.TSQL = 'update #psy ' +
'set col' + cast(@.pass as varchar) + ' = dbo.GetStringElement(f2,' + cast(@.pass as varchar) + ',default)'
, @.pass = @.pass + 1
exec(@.TSQL)
end

select * from #psy

IF objectproperty(object_id(N'GetStringElement'),'IsS calarFunction') = 1
DROP FUNCTION GetStringElement

if object_id('tempdb..#psy') is not null
drop table #psy|||if functions are not your thing you could try:
if object_id('tempdb..#psy') is not null
drop table #psy

create table #psy(f1 int identity(1,1) not null,f2 varchar(25),col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10),col5 varchar(10),col6 varchar(10))

insert into #psy (f2) values('1,2,3,4,5')
insert into #psy (f2) values('a,b,c')
insert into #psy (f2) values('George,John,Paul,Ringo')

select * From #psy

declare @.RecordID int, @.TSQL varchar(255), @.pass int
, @.Index int, @.LastIndex int, @.Return varchar(100)
, @.String varchar(100), @.Element int

select @.RecordID = min(f1) from #psy
while (@.RecordID is not null) begin
select @.String = f2 from #psy where f1 = @.RecordID

select @.Index = 1
, @.LastIndex = 0
, @.pass = 1

while (@.Index > 0) begin
select @.LastIndex = case @.Index when 1 then 0 else @.Index end
, @.Index = charindex(',',@.String,@.Index + 1)

if (@.LastIndex > 0 and @.Index = 0)
set @.Return = substring(@.String,@.LastIndex + 1, 100)
else
set @.Return = substring(@.String,@.LastIndex + 1, @.Index - @.LastIndex - 1)

select @.TSQL = 'update #psy ' +
'set col' + cast(@.pass as varchar) + ' = ''' + @.Return + ''' ' +
'where f1 = ' + cast(@.RecordID as varchar)
, @.pass = @.pass + 1

exec(@.TSQL)
end

select @.RecordID = min(f1) from #psy where f1 > @.RecordId

end

select * from #psy

looping through a recordset

Hi
I am a recordset which I would like to extract a field and make a string
from it, by appending values to it.
e.g
PolicyRef Product
C001 M
C001 B
C001 S
C002 N
C002 C
C002 T
Ideally, what I need is the products in one field:
e.g
PolicyRef Product
C001 M/B/S
C002 N/C/T
I am trying to create a Loop construct, but not having much luck.
Any ideas?
Kind Regards
RickyIf you are using SQL Server 2005 you can do this...
(taken from my blog entry:
http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
create table mailing_list (
individual_name nvarchar(100) not null,
list_name nvarchar(10) not null
)
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
C' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
A' )
select distinct
individual_name,
list = substring(
( select '/' + list_name as [text()]
from mailing_list m2
where m2.individual_name = m1.individual_name
for xml path(''), elements )
, 2, 100 )
from mailing_list m1
gives...
alex r List A
joe r List A/List B
tony r List A/List B/List C
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:O6Kep5cmGHA.4716@.TK2MSFTNGP04.phx.gbl...
> Hi
> I am a recordset which I would like to extract a field and make a string
> from it, by appending values to it.
> e.g
> PolicyRef Product
> C001 M
> C001 B
> C001 S
> C002 N
> C002 C
> C002 T
> Ideally, what I need is the products in one field:
> e.g
> PolicyRef Product
> C001 M/B/S
> C002 N/C/T
> I am trying to create a Loop construct, but not having much luck.
> Any ideas?
> Kind Regards
> Ricky
>|||Hi Tony
Thanks for your reply, is there something more dynamic, I've been told to
try and use a WHILE loop and use the ROWCOUNT, but not sure how to implment
this?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%23m4EDAdmGHA.2204@.TK2MSFTNGP03.phx.gbl...
> If you are using SQL Server 2005 you can do this...
> (taken from my blog entry:
> http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
> create table mailing_list (
> individual_name nvarchar(100) not null,
> list_name nvarchar(10) not null
> )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> A' )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> B' )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> C' )
> insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
> A' )
> insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
> B' )
> insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
> A' )
> select distinct
> individual_name,
> list = substring(
> ( select '/' + list_name as [text()]
> from mailing_list m2
> where m2.individual_name = m1.individual_name
> for xml path(''), elements )
> , 2, 100 )
> from mailing_list m1
> gives...
> alex r List A
> joe r List A/List B
> tony r List A/List B/List C
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:O6Kep5cmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>|||What version of SQL Server are you using? If you are using 2005 then use the
FOR XML below because the WHILE loops are iterative whereas the FOR XML is
set orientated so will perform signifcantly better.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:Oe%23bOEdmGHA.5040@.TK2MSFTNGP04.phx.gbl...
> Hi Tony
> Thanks for your reply, is there something more dynamic, I've been told to
> try and use a WHILE loop and use the ROWCOUNT, but not sure how to
> implment
> this?
> Kind Regards
> Ricky
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:%23m4EDAdmGHA.2204@.TK2MSFTNGP03.phx.gbl...
> SQL
>|||I'm using SS2K..
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eTkAmQdmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> What version of SQL Server are you using? If you are using 2005 then use
the
> FOR XML below because the WHILE loops are iterative whereas the FOR XML is
> set orientated so will perform signifcantly better.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:Oe%23bOEdmGHA.5040@.TK2MSFTNGP04.phx.gbl...
to
'List
'List
a
>|||Ok, here you go....
The basis of this is that you don't have that many Product's so you can
dynamically build the SQL using a cursor, so the performance comes from
orders of magnitude, for instance - far more performant to run around a
cursor once for just 5 rows then to run round a cursor once for every single
policy ref!
I'll leave you with figuring out how to get rid of the '/' on the end of the
string, should just be substring and datalength - if you get stuck then post
back.
declare cur cursor for
select distinct Product
from SourceData
declare @.product char(1)
declare @.sql_case varchar(8000)
set @.sql_case = ''
open cur
fetch next from cur into @.product
while @.@.fetch_status = 0
begin
set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
end +
'case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = ''' + @.Product +
''' ) then '''+ @.Product + '/'' else '''' end'
fetch next from cur into @.product
end
deallocate cur
declare @.sql varchar(8000)
set @.sql = '
select PolicyRef,
Concat = ' + @.sql_case + '
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
order by PolicyRef'
print @.sql
exec( @.sql )
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
> I'm using SS2K..
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eTkAmQdmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> the
> SQL
> to
> 'List
> 'List
> a
>|||Ok, so rather than leave the job half done...
declare cur cursor for
select distinct Product
from SourceData
declare @.product char(1)
declare @.sql_case varchar(8000)
set @.sql_case = ''
open cur
fetch next from cur into @.product
while @.@.fetch_status = 0
begin
set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
end +
'case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = ''' + @.Product +
''' ) then '''+ @.Product + '/'' else '''' end'
fetch next from cur into @.product
end
deallocate cur
declare @.sql varchar(8000)
set @.sql = '
select PolicyRef,
Concat = substring( Concat, 1, len( Concat ) - 1 )
from (
select PolicyRef,
Concat = ' + @.sql_case + '
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
) as c
order by PolicyRef'
print @.sql
exec( @.sql )
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> Ok, here you go....
> The basis of this is that you don't have that many Product's so you can
> dynamically build the SQL using a cursor, so the performance comes from
> orders of magnitude, for instance - far more performant to run around a
> cursor once for just 5 rows then to run round a cursor once for every
> single policy ref!
> I'll leave you with figuring out how to get rid of the '/' on the end of
> the string, should just be substring and datalength - if you get stuck
> then post back.
> declare cur cursor for
> select distinct Product
> from SourceData
> declare @.product char(1)
> declare @.sql_case varchar(8000)
> set @.sql_case = ''
> open cur
> fetch next from cur into @.product
> while @.@.fetch_status = 0
> begin
> set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
> end +
> 'case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = ''' + @.Product +
> ''' ) then '''+ @.Product + '/'' else '''' end'
> fetch next from cur into @.product
> end
> deallocate cur
> declare @.sql varchar(8000)
> set @.sql = '
> select PolicyRef,
> Concat = ' + @.sql_case + '
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> order by PolicyRef'
> print @.sql
> exec( @.sql )
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
>|||Hi Tony
Thank you for the posting, will there be a performance issue, if this is run
for many different policies?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> Ok, here you go....
> The basis of this is that you don't have that many Product's so you can
> dynamically build the SQL using a cursor, so the performance comes from
> orders of magnitude, for instance - far more performant to run around a
> cursor once for just 5 rows then to run round a cursor once for every
single
> policy ref!
> I'll leave you with figuring out how to get rid of the '/' on the end of
the
> string, should just be substring and datalength - if you get stuck then
post
> back.
> declare cur cursor for
> select distinct Product
> from SourceData
> declare @.product char(1)
> declare @.sql_case varchar(8000)
> set @.sql_case = ''
> open cur
> fetch next from cur into @.product
> while @.@.fetch_status = 0
> begin
> set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
> end +
> 'case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = ''' + @.Product +
> ''' ) then '''+ @.Product + '/'' else '''' end'
> fetch next from cur into @.product
> end
> deallocate cur
> declare @.sql varchar(8000)
> set @.sql = '
> select PolicyRef,
> Concat = ' + @.sql_case + '
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> order by PolicyRef'
> print @.sql
> exec( @.sql )
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
use
a
told
http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
from
>|||The actual SQL will produce this which is what does the work...
How many Products do you have?
This SQL will beat cursor or looping by orders of magnitude - try it.
Was the DDL you posted accurate to your own system, if not then best post
the DDL (including indexes) and I'll check to see if you'll get a good
plan...
select PolicyRef,
Concat = substring( Concat, 1, len( Concat ) - 1 )
from (
select PolicyRef,
Concat = case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'B' ) then 'B/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'C' ) then 'C/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'M' ) then 'M/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'N' ) then 'N/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'S' ) then 'S/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'T' ) then 'T/' else
'' end
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
) as c
order by PolicyRef
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:%23D2jhldmGHA.492@.TK2MSFTNGP05.phx.gbl...
> Hi Tony
> Thank you for the posting, will there be a performance issue, if this is
> run
> for many different policies?
> Kind Regards
> Ricky
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> single
> the
> post
> SQL
> use
> a
> told
> http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
> from
>|||Hi Tony
The amount of products in a policy can range from anywhere to 1 (default) to
about 5?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%237tx4pdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
> The actual SQL will produce this which is what does the work...
> How many Products do you have?
> This SQL will beat cursor or looping by orders of magnitude - try it.
> Was the DDL you posted accurate to your own system, if not then best post
> the DDL (including indexes) and I'll check to see if you'll get a good
> plan...
> select PolicyRef,
> Concat = substring( Concat, 1, len( Concat ) - 1 )
> from (
> select PolicyRef,
> Concat = case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'B' ) then 'B/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'C' ) then 'C/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'M' ) then 'M/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'N' ) then 'N/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'S' ) then 'S/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'T' ) then 'T/'
else
> '' end
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> ) as c
> order by PolicyRef
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:%23D2jhldmGHA.492@.TK2MSFTNGP05.phx.gbl...
of
'+'
a
from
r',
r',
a
luck.
>

Looping in a stored proceedure

What I would like to be able to do but am not sure if I can is the following
.
I need to set a variable = to the results of a single field recordset:
@.X = select EmployeeID from Employees where DepartmentID = 1
Then I need to build a dynamic sql statement based on the above results.
If the first recordset has three records. I need to loop through it three
times and concatinate the results of the sql that would look like this:
This or something like it would be in the loop...
@.SQL = @.SQL + 'select * from customers where EmpID = ' + @.EmpID + ','
END RESULT: @.SQL would now look like this
select * from customers where EmpID = 1, select * from customers where EmpID
= 2, select * from customers where EmpID = 3
The text between the comma's can increase or decrease depending on the
number of records in the first sql statement at the top of the page.
Any thoughts
Thank you
KentHi Kent.
You could try:
select ' select * from customers where EmpID=' + EmployeeID from
Employees where DepartmentID = 1
Bryce|||Why not just:
SELECT *
FROM Customers
WHERE empid IN
(SELECT employeeid
FROM Employees
WHERE departmentid = 1)
Dynamic SQL is bad news in a production application. Also, avoid SELECT
*. Code is safer, easier to maintain and maybe more efficient if you
list just the required column names.
David Portas
SQL Server MVP
--|||Kent,
What is the reason of doing this?
why not:
select employeeid, departmentid, ...
from employees
where departmentid = 1
AMB
"Kent Prokopy" wrote:

> What I would like to be able to do but am not sure if I can is the followi
ng.
> I need to set a variable = to the results of a single field recordset:
> @.X = select EmployeeID from Employees where DepartmentID = 1
> Then I need to build a dynamic sql statement based on the above results.
> If the first recordset has three records. I need to loop through it three
> times and concatinate the results of the sql that would look like this:
> This or something like it would be in the loop...
> @.SQL = @.SQL + 'select * from customers where EmpID = ' + @.EmpID + ','
>
> END RESULT: @.SQL would now look like this
> select * from customers where EmpID = 1, select * from customers where Emp
ID
> = 2, select * from customers where EmpID = 3
> The text between the comma's can increase or decrease depending on the
> number of records in the first sql statement at the top of the page.
> Any thoughts
> Thank you
> Kent|||I need to build an Excel report that has each Department on a diferant sheet
.
On each sheet will be a column for each employee. The number of column will
vary depending on the number of employee's in each department. So if
Department Dep1 has 5 employee's the sheet will have five columns. I could
populate each column one at a time, but would like to be able to do this
dynamicly. If posable.
"bd" wrote:

> Hi Kent.
> You could try:
> select ' select * from customers where EmpID=' + EmployeeID from
> Employees where DepartmentID = 1
> Bryce
>|||Correction,
select c.*
from customers as c inner join employees as e
on c.empid = e.employeeid and e.departmentid = 1
AMB
"Alejandro Mesa" wrote:
> Kent,
> What is the reason of doing this?
> why not:
> select employeeid, departmentid, ...
> from employees
> where departmentid = 1
>
> AMB
>
> "Kent Prokopy" wrote:
>|||My bad. Sorry I do not need * from... I need
For each employee I need a column/field.
select count(*) from DataTable where EmpID = 1 and DataDate = yesterday,
select count(*) from DataTable where EmpID = 2 and DataDate = yesterday,
select count(*) from DataTable where EmpID = 3 and DataDate = yesterday
This will give me three columns in an Excel report. or two columns or XXXXX
"David Portas" wrote:

> Why not just:
> SELECT *
> FROM Customers
> WHERE empid IN
> (SELECT employeeid
> FROM Employees
> WHERE departmentid = 1)
> Dynamic SQL is bad news in a production application. Also, avoid SELECT
> *. Code is safer, easier to maintain and maybe more efficient if you
> list just the required column names.
> --
> David Portas
> SQL Server MVP
> --
>|||select EmpID , count(*) from DataTable
where DataDate = yesterday
GROUP BY EmpID
and do the pivoting in Excel.
Jacco Schalkwijk
SQL Server MVP
"Kent Prokopy" <KentProkopy@.discussions.microsoft.com> wrote in message
news:2126E40D-33DA-4389-A865-73E33F716A0D@.microsoft.com...
> My bad. Sorry I do not need * from... I need
> For each employee I need a column/field.
> select count(*) from DataTable where EmpID = 1 and DataDate = yesterday,
> select count(*) from DataTable where EmpID = 2 and DataDate = yesterday,
> select count(*) from DataTable where EmpID = 3 and DataDate = yesterday
> This will give me three columns in an Excel report. or two columns or
> XXXXX
> "David Portas" wrote:
>|||Thank you all for your help/thoughts.
I have come up with a solution that will work.
I am going to build the sql statement in vb code and pass it tp the SP as a
varchar.
"David Portas" wrote:

> Use an Excel Pivot Table for that. You can query the database directly
> and it will create the columns for you. Alternatively you could use
> DTS.
> --
> David Portas
> SQL Server MVP
> --
>|||> I have come up with a solution that will work.
> I am going to build the sql statement in vb code and pass it tp the SP as
a
> varchar.
Ugh, WHY? Sure, that will *work* but it is far and away from the best
solution. This is like going to the grocery store with a list of bar codes
for the products you want to buy.

Loop with calculation

I have a table which contains numerical data in a field called active_en_del. I need to loop through this table and perform a calculation where row 2 minus row 1 = and store into another field; row 3 - row 2 = and store into another field etc. How would I perform this? Thanks

I am not sure which DB you are using. You also say that you want to store the difference in different "field"s. I am not sure if by fields, you mean columns or what. Using this below approach, you can get all the results in rows:

Assuming sql server 2005, you can get rownumbers for all the rows using row_number() over(order by id)

and then do a self join.

Code Snippet

select a.col1-b.col1 from

(select col1, row_number() over(order by id) as rowid
from tblData)
a,

(select col1, row_number() over(order by id) as rowid
from tblData)

b

where a.rowid=b.rowid+1

|||My appology for not specifying. Yes it is sql 2005 and it a calculation on the same column. Thanks for the above information.sql

Loop Update

I'm trying to find a way to loop through the testnewsRecipients table and
insert the value of the USERID field equal to the USERID field in the
tempUsers table. As you can see in my DDL, currently the USERID field in
testnewsRecipients table is empty and the only value the 2 tables have in
common is the email field.
Can this be done with a loop statement?
DDL ****************
CREATE TABLE testUsers(
userID int NULL,
userEmail varchar(50) NULL
) ON [PRIMARY]
GO
insert into testUsers (userID, userEmail) values
('101', 'test1@.test.com')
insert into testUsers (userID, userEmail) values
('102', 'test2@.test.com')
insert into testUsers (userID, userEmail) values
('103', 'test3@.test.com')
insert into testUsers (userID, userEmail) values
('104', 'test4@.test.com')
GO
CREATE TABLE testnewsRecipients(
recipID int IDENTITY(1,1) NOT NULL,
userID int NULL,
recipEmail varchar(100) NULL
) ON [PRIMARY]
--
insert into testnewsRecipients (userID, recipEmail) values
('', 'test1@.test.com')
insert into testnewsRecipients (userID, recipEmail) values
('', 'test2@.test.com')
insert into testnewsRecipients (userID, recipEmail) values
('', 'test3@.test.com')
insert into testnewsRecipients (userID, recipEmail) values
('', 'test4@.test.com')UPDATE testnewsRecipients
SET userID = t2.userID
FROM testnewsRecipients t1
INNER JOIN testUsers t2
ON t2.userEmail = t1.recipEmail
"scott" <sbailey@.mileslumber.com> wrote in message
news:%23ru%23oiVeGHA.2188@.TK2MSFTNGP05.phx.gbl...
> I'm trying to find a way to loop through the testnewsRecipients table and
> insert the value of the USERID field equal to the USERID field in the
> tempUsers table. As you can see in my DDL, currently the USERID field in
> testnewsRecipients table is empty and the only value the 2 tables have in
> common is the email field.
> Can this be done with a loop statement?
>
> DDL ****************
> CREATE TABLE testUsers(
> userID int NULL,
> userEmail varchar(50) NULL
> ) ON [PRIMARY]
> GO
> insert into testUsers (userID, userEmail) values
> ('101', 'test1@.test.com')
> insert into testUsers (userID, userEmail) values
> ('102', 'test2@.test.com')
> insert into testUsers (userID, userEmail) values
> ('103', 'test3@.test.com')
> insert into testUsers (userID, userEmail) values
> ('104', 'test4@.test.com')
> GO
> CREATE TABLE testnewsRecipients(
> recipID int IDENTITY(1,1) NOT NULL,
> userID int NULL,
> recipEmail varchar(100) NULL
> ) ON [PRIMARY]
> --
> insert into testnewsRecipients (userID, recipEmail) values
> ('', 'test1@.test.com')
> insert into testnewsRecipients (userID, recipEmail) values
> ('', 'test2@.test.com')
> insert into testnewsRecipients (userID, recipEmail) values
> ('', 'test3@.test.com')
> insert into testnewsRecipients (userID, recipEmail) values
> ('', 'test4@.test.com')
>
>|||Thanks, but I was hoping someone would provide a "FOR" loop example for
educational purpose. I've never done a loop with sql and wanted to learn.
would a loop work on my example?
"Mike C#" <xxx@.yyy.com> wrote in message news:p%vag.3964$Xa5.673@.fe11.lga...
> UPDATE testnewsRecipients
> SET userID = t2.userID
> FROM testnewsRecipients t1
> INNER JOIN testUsers t2
> ON t2.userEmail = t1.recipEmail
> "scott" <sbailey@.mileslumber.com> wrote in message
> news:%23ru%23oiVeGHA.2188@.TK2MSFTNGP05.phx.gbl...
>|||Yeah with a CURSOR or a WHILE statement and a counter variable. Lot more
work than doing it with a single UPDATE statement however. Look up DECLARE
CURSOR (ugh) and WHILE in BOL.
"scott" <sbailey@.mileslumber.com> wrote in message
news:OLekAEWeGHA.1792@.TK2MSFTNGP03.phx.gbl...
> Thanks, but I was hoping someone would provide a "FOR" loop example for
> educational purpose. I've never done a loop with sql and wanted to learn.
> would a loop work on my example?
>
> "Mike C#" <xxx@.yyy.com> wrote in message
> news:p%vag.3964$Xa5.673@.fe11.lga...
>

Loop through each record and then each field within each record

I need to essentially do 2 loops. One loops through each record and then inside each record row, I want to perform an insert on each column.

Something like this maybe using a cursor or something else:

For each record in my table (I'll just use the cursor)
For each column in current record for cursor
perform some sql based on the current column value
Next
Next

So below, all I need to do is figure out how to loop through each column for the current record in the cursor

AS

DECLARE Create_Final_Table CURSOR FOR

SELECT FieldName, AcctNumber, Screen, CaseNumber, BKYChapter, FileDate, DispositionCode, BKUDA1, RMSADD2, RMSCHPNAME_1, RMSADDR_1,
RMSCITY_1, RMSSTATECD_1, RMSZIPCODE_1, RMSWORKKPHN, BKYMEETDTE, RMSCMPNAME_2, RMSADDR1_2, RMSCITY_2, RMSSTATECD_2,
RMSZIPCODE_2, RMSHOMEPHN, BARDATE, RMSCMPNAME_3, RMSADD1_2, RMSADD2_3, RMSCITY_3, RMSZIPCODE_3, RMSWORKPHN_2
FROM EBN_TEMP1

OPEN Create_Final_Table

FETCH FROM Create_Final_EBN_Table INTO @.FieldName, @.AcctNumber, @.Screen, @.CaseNumber, @.BKYChapter, @.FileDate, @.DispositionCode, @.BKUDA1, @.RMSADD2, @.RMSCHPNAME_1, @.RMSADDR_1,
@.RMSCITY_1, @.RMSSTATECD_1, @.RMSZIPCODE_1, @.RMSWORKKPHN, @.BKYMEETDTE, @.RMSCMPNAME_2, @.RMSADDR1_2, @.RMSCITY_2, @.RMSSTATECD_2,
@.RMSZIPCODE_2, @.RMSHOMEPHN, @.BARDATE, @.RMSCMPNAME_3, @.RMSADD1_2, @.RMSADD2_3, @.RMSCITY_3, @.RMSZIPCODE_3, @.RMSWORKPHN_2

WHILE @.@.FETCH_STATUS = 0
BEGIN

@.Chapter = chapter for this record

For each column in current record <-- not sure how to code this part is what I'm referring to

do some stuff here using sql for the column I'm on for this row

Next

Case @.Chapter
Case 7

Insert RecoverCodeRecord
Insert Status Code Record
Insert Attorney Code Record

Case 13

Insert Record
Insert Record
Insert Record

Case 11

Insert Record
Insert Record
Insert Record

Case 12

Insert Record
Insert Record
Insert Record

END

close Create_Final_Table
deallocate Create_Final_TableI need to essentially do 2 loops.Light fuse...One loops through each record and then inside each record row...stand back...I want to perform an insert on each column...cover ears...DECLARE Create_Final_Table CURSOR FOR

SELECT FieldName, AcctNumber, Screen, CaseNumber, BKYChapter, FileDate, DispositionCode, BKUDA1, RMSADD2, RMSCHPNAME_1, RMSADDR_1,
RMSCITY_1, RMSSTATECD_1, RMSZIPCODE_1, RMSWORKKPHN, BKYMEETDTE, RMSCMPNAME_2, RMSADDR1_2, RMSCITY_2, RMSSTATECD_2,
RMSZIPCODE_2, RMSHOMEPHN, BARDATE, RMSCMPNAME_3, RMSADD1_2, RMSADD2_3, RMSCITY_3, RMSZIPCODE_3, RMSWORKPHN_2
FROM EBN_TEMP1

OPEN Create_Final_Table

FETCH FROM Create_Final_EBN_Table INTO @.FieldName, @.AcctNumber, @.Screen, @.CaseNumber, @.BKYChapter, @.FileDate, @.DispositionCode, @.BKUDA1, @.RMSADD2, @.RMSCHPNAME_1, @.RMSADDR_1,
@.RMSCITY_1, @.RMSSTATECD_1, @.RMSZIPCODE_1, @.RMSWORKKPHN, @.BKYMEETDTE, @.RMSCMPNAME_2, @.RMSADDR1_2, @.RMSCITY_2, @.RMSSTATECD_2,
@.RMSZIPCODE_2, @.RMSHOMEPHN, @.BARDATE, @.RMSCMPNAME_3, @.RMSADD1_2, @.RMSADD2_3, @.RMSCITY_3, @.RMSZIPCODE_3, @.RMSWORKPHN_2

WHILE @.@.FETCH_STATUS = 0
BEGIN

@.Chapter = chapter for this record

For each column in current record <-- not sure how to code this part is what I'm referring to

do some stuff here using sql for the column I'm on for this row

Next

Case @.Chapter
Case 7

Insert RecoverCodeRecord
Insert Status Code Record
Insert Attorney Code Record

Case 13

Insert Record
Insert Record
Insert Record

Case 11

Insert Record
Insert Record
Insert Record

Case 12

Insert Record
Insert Record
Insert Record

END

close Create_Final_Table
deallocate Create_Final_Table
KABOOM!!!!!!!!!!

Why are you doing this?|||Are you trying to normalize this beast? If so, I'd do one insert operation per column in the original table. Fast, easy, clear, simple... What's not to like?

-PatP

Loop through each record and then each field within each record

I need to essentially do 2 loops. One loops through each record and then inside each record row, I want to perform an insert on each column.
Something like this maybe using a cursor or something else:
For each record in my table (I'll just use the cursor)
For each column in current record for cursor
perform some sql based on the current column value
Next
Next
So below, all I need to do is figure out how to loop through each column for the current record in the cursor

AS
DECLARE Create_Final_Table CURSOR FOR
SELECT FieldName, AcctNumber, Screen, CaseNumber, BKYChapter, FileDate, DispositionCode, BKUDA1, RMSADD2, RMSCHPNAME_1, RMSADDR_1,
RMSCITY_1, RMSSTATECD_1, RMSZIPCODE_1, RMSWORKKPHN, BKYMEETDTE, RMSCMPNAME_2, RMSADDR1_2, RMSCITY_2, RMSSTATECD_2,
RMSZIPCODE_2, RMSHOMEPHN, BARDATE, RMSCMPNAME_3, RMSADD1_2, RMSADD2_3, RMSCITY_3, RMSZIPCODE_3, RMSWORKPHN_2
FROM EBN_TEMP1
OPEN Create_Final_Table
FETCH FROM Create_Final_EBN_Table INTO @.FieldName, @.AcctNumber, @.Screen, @.CaseNumber, @.BKYChapter, @.FileDate, @.DispositionCode, @.BKUDA1, @.RMSADD2, @.RMSCHPNAME_1, @.RMSADDR_1,
@.RMSCITY_1, @.RMSSTATECD_1, @.RMSZIPCODE_1, @.RMSWORKKPHN, @.BKYMEETDTE, @.RMSCMPNAME_2, @.RMSADDR1_2, @.RMSCITY_2, @.RMSSTATECD_2,
@.RMSZIPCODE_2, @.RMSHOMEPHN, @.BARDATE, @.RMSCMPNAME_3, @.RMSADD1_2, @.RMSADD2_3, @.RMSCITY_3, @.RMSZIPCODE_3, @.RMSWORKPHN_2
WHILE @.@.FETCH_STATUS = 0
BEGIN
@.Chapter = chapter for this record
For each column in current record <- not sure how to code this part is what I'm referring to
do some stuff here using sql for the column I'm on for this row

Next
Case @.Chapter
Case 7

Insert RecoverCodeRecord
Insert Status Code Record
Insert Attorney Code Record
Case 13
Insert Record
Insert Record
Insert Record
Case 11
Insert Record
Insert Record
Insert Record
Case 12
Insert Record
Insert Record
Insert Record
END
close Create_Final_Table
deallocate Create_Final_Table

Also, if you think there is a better way to do this, let me know.

Are you inserting from EBN_TEMP1 into multiple tables? If so then you can just use series of INSERT...SELECT statements. You need to reference the column you need in each SELECT statement.|||

I have to take every record from my select, cycle through each. So let's say I cycle to the first record in my cursor. I need to then cycle through each field in that row and take that field and do something with it.

Then move on to the next row, cycle through it's fields one by one and so on till I have done this for every row in my cursor. I just don't know how to cycle and reference each column in a unique row after each iteration of my cursor's rows

What I'll be doing wtih each colum is taking the value and inserting it into another table with some other values I'll specify in a select.

|||There must be a way to do a loop to go through each field in a cursor row, but I haven't come up with any and have searched internet forever. This is shocking that nobody has ever brought this up. All they talk about is looping through a cursor's rows or just rows in general, not how to take a row and loop through to do something with every single column (field) in the row. I have a good reason for this need so please don't ask why if you're tempted to.|||

I'm not trying to be rude whatsoever but to me that's inefficient to create multiple inserts and selects. But of course you probably didn't know that those selects and inserts would be inserting the same values, only the field value is changing in the statement at each iteration. So that's why I don't want to basically rewrite the same insert and select. I just need to loop through each and move in the value to a parameter in my insert statement

|||

SQL is not a procedural language so it is best to approach the problem with a set oriented mindset. And this is often hard to do. So if you can perform the operation efficiently using DMLs alone it is much more efficient for the engine and it is also easier for you to maintain the code. Let's take an example. (You have to provide some examples as to what you are doing in the insert. You didn't answer my question about whether you are inserting into multiple tables)

insert into t1 (f1, f2)

select f1, f2 -- any computations on the columns can be done here

from tbl

....

insert into t1 (f3, f4)

select f3, f4 -- any computations on the columns can be done here

from tbl

....

So there is nothing like looping through each column. There simply isn't any construct in TSQL or similar procedural languages in RDBMSes. On the other hand if you want to unpivot the results then you can do that using UNPIVOT operator in SQL Server 2005 or use SQL again. To use SQL to unpivot the operation of converting columns to rows then you can do something like below:

-- traditional SQL way

select f1

from tbl

...

union all

select f2

from tbl

....

-- another less obvious method

select case c.c when 1 then f1 when 2 then f2 end as f

from tbl

cross join (select 1 union all select 2) as c(c)

If you do not want to repeat the query multiple times then you can define a view or inline table-valued function or temporary table or table variables and use it instead. So there are many ways to avoid duplication of code. Best is to describe your problem rather than showing procedural code since there are many ways to perform the same set of operations in SQL much more efficiently and elegantly.

|||

My insert will look lik this and all go into one table because that table will end up being the flat file I create

Insert into table1 'a1', 'b1', @.ColumnName, @.ColumnValue, 'IO'

so for each column in the row, I have to insert it as a separate record into my final table.

Yes, this is inefficient but I have to do this for our stupid ERP system which whose UI only can map updates based on individual field records from a flat file....don't ask me why, it's retarted. they will take my flat file an use it in conjunctiuon with the ERP Import GUI to do so, I just have to create the flat file. Before the process was:

1) receive txt comma delimited file from our vendor

2) Parse it out into an MS Access Table

3) Create an individual record for each column in each row and include the AccountID with it and some other static values

4) save it as a fixed length flat file

Now I'm automating this process for them using SQL Server 2005 Integration Services. My flow is like this:

1) Use Flat File Source to import the comma delimmeted txt file (650,000 records)

2) Use Conditional Split to determine which records to filter out

3) Use OLE DB Destination Editor to move in the records to a table

4) Use a SQL TASK to code the splitting out of each field of each row into a new record in my final table. The final table will be used to create the fixed length flat file in the end.

#4 is what I'm trying to do. I have to include the following fields for each record in my final table:

AccountID, 'a1', 'b1', ColumnName, ColumnValue

So in other words for each row in my table that the OLE DB added my records to, I then have to split out each column for each row into a final table including the account D for every row.

I hope this makes sense, it's not as confusing as it seems.

|||

so expanding on my last post, this may give you a sense:

Let's say the OLE DB moves my records into a table initially for step 3. The table now looks something like this:
Acct # Zip Phone Addr
11223 23232 333-444-5555 6556 Duns Rd.
12345 34343 222-444-3333 1000 Aspire Blvd.
I need to create a record using the Acct # and column for each column as well as append some other values like this into a final table. That final table will be a flat file in the end, I just need to figure out how to get this done first.
11223 23232 othervalue1 othervalue2
11223 333-444-5555 othervalue1 othervalue2
11223 6556 Duns Rd. othervalue1 othervalue2
12345 34343 othervalue1 othervalue2
12345 222-444-3333 othervalue1 othervalue2
12345 1000 Aspire Blvd. othervalue1 othervalue 2

|||If you are using SSIS then there is really no reason to denormalize the data in SQL Server. You can just do it in SSIS. Look at the foreach loop container in SSIS. This should allow you to loop through each column. If you have more questions about SSIS please post in the SQL Server Integration Services forum.|||ok, so then if I use the for each, how do I add my sql statement and have it refer to each column for the row I'm on?|||No. You get the data from the table as is and then perform the transformation on the client side. This is easier to do. For example, if you get a datareader for the results then you can use the columns collection with foreach container and loop through each column. If you post the question in the SSIS forum you will get more solutions.|||thanks so much|||

I am following your advice on an SSIS package I have that must evaluate each record. The issue I am having is that the dataReader destination is far slower then the recordset destination. Problem is I can not figure out how to get data from the record set.

Loop through each record and then each field within each record

I need to essentially do 2 loops. One loops through each record and then inside each record row, I want to perform an insert on each column.
Something like this maybe using a cursor or something else:
For each record in my table (I'll just use the cursor)
For each column in current record for cursor
perform some sql based on the current column value
Next
Next
So below, all I need to do is figure out how to loop through each column for the current record in the cursor

AS
DECLARE Create_Final_Table CURSOR FOR
SELECT FieldName, AcctNumber, Screen, CaseNumber, BKYChapter, FileDate, DispositionCode, BKUDA1, RMSADD2, RMSCHPNAME_1, RMSADDR_1,
RMSCITY_1, RMSSTATECD_1, RMSZIPCODE_1, RMSWORKKPHN, BKYMEETDTE, RMSCMPNAME_2, RMSADDR1_2, RMSCITY_2, RMSSTATECD_2,
RMSZIPCODE_2, RMSHOMEPHN, BARDATE, RMSCMPNAME_3, RMSADD1_2, RMSADD2_3, RMSCITY_3, RMSZIPCODE_3, RMSWORKPHN_2
FROM EBN_TEMP1
OPEN Create_Final_Table
FETCH FROM Create_Final_EBN_Table INTO @.FieldName, @.AcctNumber, @.Screen, @.CaseNumber, @.BKYChapter, @.FileDate, @.DispositionCode, @.BKUDA1, @.RMSADD2, @.RMSCHPNAME_1, @.RMSADDR_1,
@.RMSCITY_1, @.RMSSTATECD_1, @.RMSZIPCODE_1, @.RMSWORKKPHN, @.BKYMEETDTE, @.RMSCMPNAME_2, @.RMSADDR1_2, @.RMSCITY_2, @.RMSSTATECD_2,
@.RMSZIPCODE_2, @.RMSHOMEPHN, @.BARDATE, @.RMSCMPNAME_3, @.RMSADD1_2, @.RMSADD2_3, @.RMSCITY_3, @.RMSZIPCODE_3, @.RMSWORKPHN_2
WHILE @.@.FETCH_STATUS = 0
BEGIN
@.Chapter = chapter for this record
For each column in current record <- not sure how to code this part is what I'm referring to
do some stuff here using sql for the column I'm on for this row

Next
Case @.Chapter
Case 7

Insert RecoverCodeRecord
Insert Status Code Record
Insert Attorney Code Record
Case 13
Insert Record
Insert Record
Insert Record
Case 11
Insert Record
Insert Record
Insert Record
Case 12
Insert Record
Insert Record
Insert Record
END
close Create_Final_Table
deallocate Create_Final_Table

Also, if you think there is a better way to do this, let me know.

Are you inserting from EBN_TEMP1 into multiple tables? If so then you can just use series of INSERT...SELECT statements. You need to reference the column you need in each SELECT statement.|||

I have to take every record from my select, cycle through each. So let's say I cycle to the first record in my cursor. I need to then cycle through each field in that row and take that field and do something with it.

Then move on to the next row, cycle through it's fields one by one and so on till I have done this for every row in my cursor. I just don't know how to cycle and reference each column in a unique row after each iteration of my cursor's rows

What I'll be doing wtih each colum is taking the value and inserting it into another table with some other values I'll specify in a select.

|||There must be a way to do a loop to go through each field in a cursor row, but I haven't come up with any and have searched internet forever. This is shocking that nobody has ever brought this up. All they talk about is looping through a cursor's rows or just rows in general, not how to take a row and loop through to do something with every single column (field) in the row. I have a good reason for this need so please don't ask why if you're tempted to.|||

I'm not trying to be rude whatsoever but to me that's inefficient to create multiple inserts and selects. But of course you probably didn't know that those selects and inserts would be inserting the same values, only the field value is changing in the statement at each iteration. So that's why I don't want to basically rewrite the same insert and select. I just need to loop through each and move in the value to a parameter in my insert statement

|||

SQL is not a procedural language so it is best to approach the problem with a set oriented mindset. And this is often hard to do. So if you can perform the operation efficiently using DMLs alone it is much more efficient for the engine and it is also easier for you to maintain the code. Let's take an example. (You have to provide some examples as to what you are doing in the insert. You didn't answer my question about whether you are inserting into multiple tables)

insert into t1 (f1, f2)

select f1, f2 -- any computations on the columns can be done here

from tbl

....

insert into t1 (f3, f4)

select f3, f4 -- any computations on the columns can be done here

from tbl

....

So there is nothing like looping through each column. There simply isn't any construct in TSQL or similar procedural languages in RDBMSes. On the other hand if you want to unpivot the results then you can do that using UNPIVOT operator in SQL Server 2005 or use SQL again. To use SQL to unpivot the operation of converting columns to rows then you can do something like below:

-- traditional SQL way

select f1

from tbl

...

union all

select f2

from tbl

....

-- another less obvious method

select case c.c when 1 then f1 when 2 then f2 end as f

from tbl

cross join (select 1 union all select 2) as c(c)

If you do not want to repeat the query multiple times then you can define a view or inline table-valued function or temporary table or table variables and use it instead. So there are many ways to avoid duplication of code. Best is to describe your problem rather than showing procedural code since there are many ways to perform the same set of operations in SQL much more efficiently and elegantly.

|||

My insert will look lik this and all go into one table because that table will end up being the flat file I create

Insert into table1 'a1', 'b1', @.ColumnName, @.ColumnValue, 'IO'

so for each column in the row, I have to insert it as a separate record into my final table.

Yes, this is inefficient but I have to do this for our stupid ERP system which whose UI only can map updates based on individual field records from a flat file....don't ask me why, it's retarted. they will take my flat file an use it in conjunctiuon with the ERP Import GUI to do so, I just have to create the flat file. Before the process was:

1) receive txt comma delimited file from our vendor

2) Parse it out into an MS Access Table

3) Create an individual record for each column in each row and include the AccountID with it and some other static values

4) save it as a fixed length flat file

Now I'm automating this process for them using SQL Server 2005 Integration Services. My flow is like this:

1) Use Flat File Source to import the comma delimmeted txt file (650,000 records)

2) Use Conditional Split to determine which records to filter out

3) Use OLE DB Destination Editor to move in the records to a table

4) Use a SQL TASK to code the splitting out of each field of each row into a new record in my final table. The final table will be used to create the fixed length flat file in the end.

#4 is what I'm trying to do. I have to include the following fields for each record in my final table:

AccountID, 'a1', 'b1', ColumnName, ColumnValue

So in other words for each row in my table that the OLE DB added my records to, I then have to split out each column for each row into a final table including the account D for every row.

I hope this makes sense, it's not as confusing as it seems.

|||

so expanding on my last post, this may give you a sense:

Let's say the OLE DB moves my records into a table initially for step 3. The table now looks something like this:
Acct # Zip Phone Addr
11223 23232 333-444-5555 6556 Duns Rd.
12345 34343 222-444-3333 1000 Aspire Blvd.
I need to create a record using the Acct # and column for each column as well as append some other values like this into a final table. That final table will be a flat file in the end, I just need to figure out how to get this done first.
11223 23232 othervalue1 othervalue2
11223 333-444-5555 othervalue1 othervalue2
11223 6556 Duns Rd. othervalue1 othervalue2
12345 34343 othervalue1 othervalue2
12345 222-444-3333 othervalue1 othervalue2
12345 1000 Aspire Blvd. othervalue1 othervalue 2

|||If you are using SSIS then there is really no reason to denormalize the data in SQL Server. You can just do it in SSIS. Look at the foreach loop container in SSIS. This should allow you to loop through each column. If you have more questions about SSIS please post in the SQL Server Integration Services forum.|||ok, so then if I use the for each, how do I add my sql statement and have it refer to each column for the row I'm on?|||No. You get the data from the table as is and then perform the transformation on the client side. This is easier to do. For example, if you get a datareader for the results then you can use the columns collection with foreach container and loop through each column. If you post the question in the SSIS forum you will get more solutions.|||thanks so much|||

I am following your advice on an SSIS package I have that must evaluate each record. The issue I am having is that the dataReader destination is far slower then the recordset destination. Problem is I can not figure out how to get data from the record set.

Loop insert

Hi,

I will do my best to explain my question:

I have a field called CUSTOMER which contains a number such as C00001,
C0002 etc.

However i now have a new field called XCUSTOMER which is the new number that they relate to.

CUSTOMER C00001 now relates to XCUSTOMER 493845.
CUSTOMER C00002 now relates to XCUSTOMER 494343.

Basically there are hundreds of these and i dont have the time to manually enter the data. I just want to say "where customer = C00001 then insert 49494 into XCUSTOMER and then loop through and insert them all.

My table is called CUSTOMERINFO.

If anyone could help it would be much apprieciated as it would save me so much time.

Thanks:)update customerinfo
set xcustomer = case customer
when 'c00001' then 493845
when 'c00001' then 494343
...
end

Lookup value for a field?

Using VS2005 and creating reports. GUI has combo boxes with lookups. The
main table is storing the selected.value of the combo box, but the combo box
is displaying the "Name" column.
How do I display the "Name" column value on a report for a field instead of
the integer value of the row?
Thanks.If you look at the different options in the parameter creation window, it's
pretty straightforward.
1) While on the "Data" tab, go to "Report | Report Parameters" in the menu.
2) Click on "Add".
3) Give a meaningful name to your parameter (other than
"Report_Parameter_0")
4) Select a datatype (string, int, etc.). In your case, this would be int.
5) Type a prompt for your parameter (this will appear to the left of your
parameter combobox in the browser).
6) In the "Available Values" section, select "From Query"
7) Select the dataset you will use for your parameter values (typically a
separate dataset from the report's main dataset).
8) Put your key field (the integer value) in the "Value Field" drop down.
9) Preview, Rebuild or redeploy your report and voilà!
HTH,
Alain
"Brooke" <tbrooked@.hotmail.com> wrote in message
news:%23dSGgzfrHHA.5024@.TK2MSFTNGP04.phx.gbl...
> Using VS2005 and creating reports. GUI has combo boxes with lookups. The
> main table is storing the selected.value of the combo box, but the combo
> box is displaying the "Name" column.
> How do I display the "Name" column value on a report for a field instead
> of the integer value of the row?
> Thanks.
>

Lookup Functionality

Hi,

I have 3 tables that i gather a single field from each and put them into another table.. simple enough you might think

but when i start using a lookup funciton to check if i have already entered an item (this package could be run a number of times) some duplicates slip though, and as i continue to run the pakcage less and less appear, but never reaching 0

very strange..

doing select statements on the target table reveal strange behaviour.. after the first 'iteration' there are only unique entries in the target table (exactly what i want). After the 2nd, 3rd, 4th etc duplicates appear of upper and lower case

i can only assume that there is something wrong with my query,,, but running it in SQL Manager reveals ~40,000 rows no matter how many times i run it...

more magically appear when hte same statement is ran in SSIS!!

I have tried a view, and using test tables (rather the live ones i am working on) and the reuslt is the same...

any help would be muc happreciated

regards

Chris

You might want to make sure you are converting the values for the Lookup and the value you are matching on to UPPERCASE to avoid any case mismatches.

|||

Hi, thanks for your post.. i have already put it into uppercase, but this has not solved the problem completely..

i still don't understand how the query the first time returns all the values as it should, and when it is re-ran more magically appear but in different cases (there is also an issue with foreign text, but thats something different)

one of my tables is nvarchar, and to accomodate this, the target table for all the entries is nvarchar therefore needing the items entered into it to be converted from varchar

could this be causing the problem perhaps? something to do with unicode / non-unicode?

cheers

Chris

|||

Is the problem only in the Source adapter? If you add a RowCount immediately after the Source, is it returning a variable number of rows?

|||

Hi,

The source returns the same number of rows each time, but for some reason is failing to match them up when doing a lookup and thus insertng duplicates...

one such example is:

select * from Snowflake.DimCity

where city = 'zweibrücken'

zweibrucken 2007-08-01 09:33:00

zweibrücken 2007-08-01 09:34:00

how is this discrepancy being missed first time around?!?

my SELECT statement converts all the city fields to nvarchar on select... however i have to do this becasue some of the source tables are nvarchar and some arent, and SSIS throws errors if i don't convert ;(

help help

|||

You are aware that the lookup caches all of its data at the beginning of the data flow, aren't you? So, by default, it can't match duplicates on rows that you are inserting in the same data flow? You can work around this by using an aggregate transform to eliminate the duplicates, or by disabling caching on the lookup (found on the advanced tab), which forces it to query the database for each row. Unfortunately, disabling caching makes it run slower, and doesn't guarantee you won't get duplicates, because of the way rows are handled in batches.

Also, I am fairly positive that because "u" and "ü" are two different characters, the lookup will not match them. If you looked at the actual bytes making up those two strings, they would be different. You might need to use the Fuzzy Lookup to do your matching in this scenario.

|||Hi,

I have solved this issue now, I converted all the fields in use to nvarchar in the query.. and then the collation of the fileds used in the database to Latin1_General_BIN2

From SQL Server Developer Centre:

"Sorts and compares data in SQL Server tables based on Unicode code points for Unicode data. For non-Unicode data, Binary-code point will use comparisons identical to binary sorts.

The advantage of using a Binary-code point sort order is that no data resorting is required in applications that compare sorted SQL Server data. As a result, a Binary-code point sort order provides simpler application development and possible performance increases."

This is basically what you said in your last post jwelch, comparing the acutal bytes and all seems to be well now!! I no longer have spontaneuously appearing entreis when i run the SSIS package.

Thank you

Chris

looking to collect distinct date part out of datetime field

from this, circdate being a datetime field:
SQLQuery = "select distinct circdate from circdata order by circdate"

I need the distinct date portion excluding the time part.

this has come about when I discovered
I am inserting and updating some datetime values with the same value,
but for some reason, the values are always off by a few seconds. I set
a variable called SetNow assigned to NOW and then set the datetime
fields to this SetNow variable. Then when I collect the distinct date
time I am assuming they will have the same values recorded in
circdate, but no, they are off by several seconds. Makes no sense to me
at all. I tried renaming the variable several times but it makes no
difference at all.
any help appreciated, thanks.SQLQuery = "SELECT distinct CONVERT(char,circdate,1) from circdata"

I think I solved it
any one see a problem with this?
thanks
how does the '1' parameter affect the output as I know there are
several choices|||sdowney717@.msn.com (sdowney717@.msn.com) writes:
> SQLQuery = "SELECT distinct CONVERT(char,circdate,1) from circdata"
> I think I solved it
> any one see a problem with this?
> how does the '1' parameter affect the output as I know there are
> several choices

1 is a format parameter that controls how the datetime value is formatted.
You can read about these in the topic CAST and CONVERT in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||http://www.aspfaq.com/show.asp?id=2464
shows all the outputs
Was wondering though if it wont use an index on a convert.|||sdowney717@.msn.com (sdowney717@.msn.com) writes:
> http://www.aspfaq.com/show.asp?id=2464
> shows all the outputs
> Was wondering though if it wont use an index on a convert.

For the query you gave,

SELECT distinct CONVERT(char,circdate,1) from circdata

this is not an issue. If there is an index on cricdate, SQL Server will
use that index in the most effective, that is to scan the index, because
that is what the query calls for, with or without the convert().

On the other hand

SELECT col1, col2, col3 FROM circdata
WHERE CONVERT(char, circdate, 1) = @.val

will probably not use the index, and in any case the query will not seek
the index, that is lookup the value through the B-tree. This is because
the index is sorted on the datetime value, not on a character value.

To list all rows for a given date you can do:

SELECT col1, col2, col3 FROM circdata
WHERE circdate >= @.val AND circdate < dateadd(DAY, @.val, 1)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Also refer this to know how to query on dates
http://www.karaszi.com/SQLServer/info_datetime.asp

Madhivanan

looking into rdl file

Some business analysts would like to reverse engineer the ssrs .rdl files by looking into the field mappings and data sources. Do they have to install the client version of visual studio or there is an easier way?

TIA..

You can open the RDL files in Notepad and see that they are written in XML. You can find the query run by looking for the <CommandText> tag, and each field is listed under a <ReportItems> tag. The data source information is also listed in there, under the <ConnectionProperties> tag.

Hope this helps.

Jarret