Better way to perform a query than using cursors

Morning all,
I have a situation where i have to put a couple of tables into a flat file output. the tables are designed as below

Table1
AppID, Name, address
Table 2
Appid, DeclineReason
They are joined by AppID. Table 2 can have multiple (up to 10) decline reasons for 1 application id.
My task was to put the output into one file, so what i did was setup 10 variables for the 10 possible decline reasons then a cursor to get the distinct appID's from table 1, then within the cursor loop i declared another cursor which started with @DeclineReason1 and went through each Decline reason for that appid. At the end it did an insert into a table which looked like this...
AppID,Name, Address, DecReason1,DecReason2,DecReason3,DecReason4.....
Is there a better way than doing these nested triggers? Im sure there must be, but i couldnt think of any at the time. Your advice would be appreciated.

Thanks
John

If an 8000 character restriction is not a problem (4000 if DeclineReason is a unicode column) ...
DECLARE @CheckId INT, @Output VARCHAR(8000)
SELECT @CheckId = AppId
FROM Table1
WHERE xxxxxxxxxxxxxxxxxxx
SET @Output = CAST(@CheckId AS VARCHAR(100))
SELECT @Output = @Output + ',' + DeclineReason
FROM Table2
WHERE AppId = @CheckId
ORDER BY DeclineReason
SELECT @Output

Hi Adriaan,
Thanks for the reply, however this method will not work as each decline reason has to be in a seperate field, not all in one. the output has to be like
AppID, Name, Addy, Declinereason1, declinereason2....
not
Appid,Name, Addy, declinereason
ps, anandchatterjee i will check out the link - thanks

Denormalizing data is a common cause for bad headaches. Don't you love it.

yeah its just fantastic
The solution i presented in my first post does work, i just thought that there would be a quicker, or better written solution. Maybe there isnt, hence the post

Did you check out if they would accept XML?
You can't do much with XML in SQL 7.0 or 2000, but it's quite simple: you add a FOR XML clause to a regular SELECT query. Check it out in Books Online.

Madhivanan - cheers, ill check that out.

Adriaan - Would be great if they did accept XML, however i tried that one already and they cant. they wont even accept a csv!!

By the way, my code actually does generate a comma-separated list with as many commas as are needed for the DeclineReason values.
The only thing it doesn't do is create a header row with field names ...

Plzz check this URL:
http://sql-server-performance.com/Community/forums/t/20590.aspx

AM,
you could always use a PIVOT operator as follows:
declare @t1 table (
i int)

insert @t1 (i)
select 1 union
select 2 union
select 3 union
select 4

declare @t2 table (
i int, val int)

insert @t2 ( i, val )
select 1, 0 union
select 1, 1 union
select 1, 2 union
select 2, 0 union
select 2, 1 union
select 3, 0 union
select 3, 1 union
select 3, 2 union
select 3, 3 union
select 3, 4 union
select 3, 5 union
select 4, 0 union
select 4, 1 union
select 4, 2 union
select 4, 3 union
select 4, 4

select * from
(
select t1.i, t2.val from @t1 t1
join @t2 t2 on t1.i = t2.i
) a
pivot ( count(val) for val in ( [0], [1], [2], [3], [4], [5] ) ) as p
Results:
i 0 1 2 3 4 5
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 0 0 0
2 1 1 0 0 0 0
3 1 1 1 1 1 1
4 1 1 1 1 1 0

Using your example:
declare @t1 table (
AppID int, Name char(25), address char(30))

insert @t1 (AppID, Name, address)
select 1, 'Mark', '123 main' union
select 2, 'Shannon', '111 field st' union
select 3, 'Carl', '325 james ave' union
select 4, 'Pete', '677 436th st'

declare @t2 table (
AppID int, Declinereason char(25))

insert @t2 ( AppID, Declinereason )
select 1, 'Declinereason0' union
select 1, 'Declinereason1' union
select 1, 'Declinereason2' union
select 2, 'Declinereason0' union
select 2, 'Declinereason1' union
select 3, 'Declinereason0' union
select 3, 'Declinereason1' union
select 3, 'Declinereason2' union
select 3, 'Declinereason3' union
select 3, 'Declinereason4' union
select 3, 'Declinereason5' union
select 4, 'Declinereason0' union
select 4, 'Declinereason1' union
select 4, 'Declinereason2' union
select 4, 'Declinereason3' union
select 4, 'Declinereason4'



select * from
(
select t1.AppID, t1.Name, t1.address, t2.Declinereason from @t1 t1
join @t2 t2 on t1.AppID = t2.AppID
) a
pivot ( count(Declinereason) for Declinereason in ( [Declinereason0], [Declinereason1], [Declinereason2], [Declinereason3], [Declinereason4], [Declinereason5] ) ) as p

oops... sorry for the 2005 answer... was in the wrong forum.

Please try this: it might perform better; usually even temp tables do better than cursors [].
CREATE TABLE #DeclineReasons (
id INT IDENTITY(1, 1),
Appid INT, --<<-- chg to match original datatype on table
DeclineReason VARCHAR(30), --<<-- chg to match original datatype on table
PRIMARY KEY (Appid, id) --NOTE: do not remove, needed for performance
)
INSERT INTO #DeclineReasons (Appid, DeclineReason)
SELECT Appid, DeclineReason
FROM [Table 2]
ORDER BY Appid, DeclineReason --NOTE:do not remove, needed for performance [and accuracy]
SELECT t1.Appid, t1.Name, t1.Address,
MAX(CASE WHEN dr2.id = dr1.firstId THEN dr2.DeclineReason ELSE '' END) AS DecReason1,
MAX(CASE WHEN dr2.id = dr1.firstId + 1 THEN dr2.DeclineReason ELSE '' END) AS DecReason2,
MAX(CASE WHEN dr2.id = dr1.firstId + 2 THEN dr2.DeclineReason ELSE '' END) AS DecReason3,
--...4 thru 9 same general approach as above
MAX(CASE WHEN dr2.id = dr1.firstId + 9 THEN dr2.DeclineReason ELSE '' END) AS DecReason10
FROM [Table 1] t1
INNER JOIN (
SELECT Appid, MIN(id) AS firstId
FROM #DeclineReasons
GROUP BY Appid
) AS dr1 ON dr.Appid = t1.Appid
INNER JOIN #DeclineReasons dr2 ON dr2.Appid = t1.Appid
GROUP BY t1.Appid, t1.Name, t1.Address