quote:

---

Originally posted by sapator

Hi.
Strangely i cannot find a decent example.
I don't want to use cursors if possible.

I have a table
id, name, specialid
1,john,3
2,mike,3
4,mary,3
5,nick,6
6,john,6
8,kate,6

etc.
So i want a loop that will pick the, let's say the name column and the id column and will display all the names and id's that is on specialid =3 .
Do i need a temp table. I say a while that extended the id but the id's here are not in continues order so i can do a -1 .
Thanks.

---

SELECT id, name FROM YourTable WHERE specialid=3

-- create dummy table and load with original test data
CREATE TABLE dbo.test_data (
    id int,
    name varchar(30),
    specialid int
    )
INSERT INTO dbo.test_data
SELECT *    
FROM (
    SELECT 1 AS id,'john' AS name,3 AS specialid UNION ALL
    SELECT 2,'mike',3 UNION ALL
    SELECT 4,'mary',3 UNION ALL
    SELECT 5,'nick',6 UNION ALL
    SELECT 6,'john',6 UNION ALL
    SELECT 8,'kate',6
) AS test_data


SELECT td.specialid, ca1.ids_and_names
FROM (
    SELECT DISTINCT specialid
    FROM dbo.test_data td
) AS td
CROSS APPLY (
    SELECT STUFF((
        SELECT ';' + CAST(td2.id AS varchar(10)) + ',' + td2.name
        FROM dbo.test_data td2
        WHERE
            td2.specialid = td.specialid
        ORDER BY td2.id
        FOR XML PATH('')
    ), 1, 1, '') AS ids_and_names
) AS ca1

DROP TABLE dbo.test_data

quote:

---

Originally posted by sapator

Hi. Let me explain a little better, so i need to parse the rows one by one (i don't care about the order) and do something with, let's say name and id.
So on Jame's answer, how would i do something on each row at a time. I suspect a temp table? Let's say i need to get the id on each row in the loop and just print it out, for testing.
Michael, when you say while @i<11 do i suspect that i need first to do a count on the rows and set @i with that? So i am not completely sure on how would i get the row values i need, @i will just loop the int.How would i get the id of the first row in?
Scott, can you show me where i put the logic to get, for example the row id, this will just put everything in a row. I don't to have to parse the created row, seems like a double job.

---

SELECT id  FROM YourTable

UPDATE YourTable SET id = id+10;

UPDATE YourTable SET name = name+'.' WHERE specialid=3

DECLARE @BusinessCursor as CURSOR;

SET @BusinessCursor = CURSOR FOR
select fieldSize,fieldName from DBTempSyncScenarioFD 
where ScenarioAA = @ScenarioAA 
order by FieldPosition;
OPEN @BusinessCursor;
FETCH NEXT FROM @BusinessCursor INTO @field_size, @field_name;

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @dynamicColumn NVARCHAR(MAX) = -- @field_size, @field_name of
the dynamic created column ;
DECLARE @dynamicSQL NVARCHAR(MAX) = 
-- alert the table and insert the dynamic column
EXECUTE (@dynamicSQL) ;

 FETCH NEXT FROM @BusinessCursor INTO @field_size, @field_name;
END
CLOSE @BusinessCursor;
DEALLOCATE @BusinessCursor;

quote:

---

Originally posted by James K

Instead of thinking about HOW you want to accomplish something, think about WHAT you want to accomplish.

---