I am working on a stored procedure that will trigger once a week so I am not worried about speed as much as getting the thing to 开发者_运维技巧work.
What I am trying to do is loop through a table called households
that stores information about the houses, then using the houseid
looping through a table called individuals
which will returns more than one person therefore leading me to loop through that result to gather information.
My main goal is to concatenate all the names of the people in the household, and their codes
declare @RowNum int,
@houseId nchar(10),
@hid nchar(25)
select @houseId=MAX(ID) FROM households --start with the highest ID
Select @RowNum = Count(*) From households --get total number of records
WHILE @RowNum > 0 --loop until no more records BEGIN
select @hid = id from households where ID = @houseid --get other info from that row
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(ID) FROM (select id, firstname,lastname,party_code from indviduals where householdid = @houseid) as table1)
DECLARE @code1 VARCHAR(50), @names1 VARCHAR(500), @lastname1 VARCHAR(50)
-- Declare an iterator
DECLARE @I INT
-- Initialize the iterator
SET @I = (select min(id) from indviduals where householdid = @houseid)
-- Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
BEGIN
-- Declare variables to hold the data which we get after looping each record
DECLARE @code VARCHAR(50), @names VARCHAR(500), @lastname VARCHAR(50)
-- Get the data from table and set to variables
SELECT @code = party_code, @names = firstname, @lastname = lastname FROM (select id, firstname,lastname,party_code from indviduals where householdid = @houseid) as table1 WHERE ID = @I
-- Display the looped data
set @code1 = (@code + ',' + @code1)
set @names1 = (@names + ',' + @names1)
set @lastname1 = @lastname
-- Increment the iterator
SET @I = @I + 1
END
update households
SET firstnames=@names1, lastname=@lastname1, party_codes=@code1,
where id = @houseid
select top 1 @houseId=ID
from households
where ID < @houseID
order by ID desc --get the next one
set @RowNum = @RowNum - 1
END
household table
1 bekshire st dell MA 10001 02639 50 0002 dell NULL ALRGEN 1 BERKSHIRE ST NULL NULL NULL NULL
individuals that belong to household id 10001
first last code
BOB BUILDER U
JESS BUILDER A
i want
1 bekshire st dell MA 10001 02639 50 0002 dell NULL ALRGEN 1 BERKSHIRE ST BOB,JESS BUILDER U,A
If you are having to concatenate data to get it into the table, then your design is wrong. Please read up on normalization. A concatenated list should not be stored in a field. Each field in each row of data should have no more than 1 piece of information. What you need are related tables.
精彩评论