If the only Remark types are FV and RC you coulsd try this, or expand upon as needed. The only problem is, if you are missing the field on the left side of the join (FVremarks in this case) you will not get any record for that ID. I can hardly say this is reliable code, but if you're in a pinch this could help you.
Code:
(select a.[ID],
a.remarks as FVRemarks,
b.remarks as RCRemarks
from (select [ID], Remarks from #TEST where Type = 'FV') a
LEFT JOIN (select [ID], Remarks from #TEST where Type = 'RC') b
on a.[ID] = b.[ID])
Your table is not really set up ideally for a cross tab, as you have to group by 1 value, yet get one or more corresponding values from the same column. You could possibly create a whole set of queries as a stored proc to get desired result?
Hope this helps,
Alex
It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.
I disagree, this table is setup great for a cross tab query.
For SQL 2000 you have to bang out the code manually.
Code:
select id,
max(case when type = 'FV' then Remarks end) FVRemarks,
max(case when type = 'RC' then Remarks end) RCRemarks
from table
group by id
ordery by id
For SQL 2005 you can use the pivot function.
Code:
select id, FVRemarks, RCRemarks
from table
PIVOT
(
MAX(Remarks)
FOR Type IN ('FV', 'RC')
) as pvt
ORDER BY id
The SQL 2000 will work fine, the SQL 2005 code may need tweaking. I don't have it totally memorized yet. You can read up more on the PIVOT command in BOL under PIVOT.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.