loop through table where one field always has different value

Question

loop through table where one field always has different value

wdmm

Old Hand

Points: 321
More actions
May 2, 2016 at 2:19 pm

#326091

I need a little help with looping through a table to put all instances of the data into rows
declare @key as int
set @key = 7
declare @index as int = 0
declare @nmbrCt as int = 0
declare @rowCounter as int = 0
declare @g as varchar(50), @d as varchar(100), @C as varchar(100)
declare @dTable as table(Id varchar(100), d varchar(max))
set @nmbrCt = (select count(v) from tblOrder where key = @key)
--returns a count of 2 since there are two rows for the Key
--apple
--orange
--tblOrder
--Keysv
--72apple
--72orange
--73100
--73100
set @g = 'Value of '
if @nmbrCt > 1
begin
--get multiple entities
while @index < @nmbrCt
begin
set @rowCounter = @rowCounter + 1
set @C = (select v as cNumb from tblOrder where s = 3 and key = @key)
--this is the item that will be different
set @d = (select v as dItem from tblOrder where s = 2 and key = @key)
insert @dTable
select @key as Id, @g + @d + ' ' + @C
set @index = @index + 1
end
end
else
begin
-- this works if there is only one entity
set @C = (select v as cNumb from tblOrder where s = 3 and key = @key)
set @d = (select v as dItem from tblOrder where s = 2 and key = @key)
insert @dTable
select @key as Id, @g + @d + ' ' + @C
end
select Id,d from @dTable
--desired end results
--idd
--7Value of apple 100
--7Value of orange 100

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

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

pietlinden SSC Guru Points: 63519 More actions · Answer 1

Instead of showing us the code, please post some sample data showing the original structure of your table and then the desired one.

Most helpful would be if you would post CREATE TABLE and INSERT scripts for the source table and then a structure/sample data for the desired structure.

Instead of rehashing how to post a question, I'll point you to a super handy article... Forum Etiquette: How to post data/code on a forum to get the best help[/url].

once you do that, folks here can help you, and you'll get tested code. =)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Your data makes no sense. How do you define a relation between each fruit and its value?

There's no order in a table, you need additional values. Hopefully, this isn't a real design on a working system.

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

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

wdmm Old Hand Points: 321 More actions · Answer 3

Luis - working on a project that has it setup as follows:

key - is a foreign key to a primary key in another table

s - is a setting id -- looks like the a 2 means fruit and 3 means some type of code id

v - is a value per setting -- the value of the setting id

with a slight modification of the query below, I can get at least one row but not the other:

set @d = (select top 1 v as dItem from tblOrder where s = 2 and key = @key)

just notice i had them backwards in terms of the settings.

if the key only has 1 item in the table it works fine. I just can't quite get all the rows for multiple entities

wdmm Old Hand Points: 321 More actions · Answer 4

pietlinden,

Included in the original post:

--tblOrder

--Keysv

--72apple

--72orange

--73100

--desired end results

--idd

--7Value of apple 100

--7Value of orange 100

Below are the create and insert stmts:

--table create

CREATE TABLE [dbo].[tblOrder](

[Key] [int] NOT NULL,

[varchar](50) NOT NULL,

[v] [varchar](50) NOT NULL

) ON [PRIMARY]

-- insert

INSERT INTO tblOrder (Key, s, v)

VALUES

(7, '2', 'apple'),

(7, '2', 'orange'),

(7, '3', 100),

(7, '3', 100)

the desired is the query noted above

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

wdmm (5/2/2016)
pietlinden,
Included in the original post:
--tblOrder
--Keysv
--72apple
--72orange
--73100
--73100
--desired end results
--idd
--7Value of apple 100
--7Value of orange 100
Below are the create and insert stmts:
--table create
CREATE TABLE [dbo].[tblOrder](
[Key] [int] NOT NULL,
[varchar](50) NOT NULL,
[v] [varchar](50) NOT NULL
) ON [PRIMARY]
-- insert
INSERT INTO tblOrder (Key, s, v)
VALUES
(7, '2', 'apple'),
(7, '2', 'orange'),
(7, '3', 100),
(7, '3', 100)
the desired is the query noted above

I see nothing in the data which tells me which values go together. You can't base it on the visual order as shown as there is no order in a table.

wdmm Old Hand Points: 321 More actions · Answer 6

yah I see that now so I requested a new col and that contain an id to link the rows; modified the query and it works