Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
footohi
Starting Member
10 Posts |
 Posted - 2006-04-26 : 18:40:10
|
| I'm trying to make a flat file for our DB2 db, but of course I have relational tables that return more than one row. I thought I'd be able to resolve it by using a Stored Proc I found here for SQL 2000 called crosstab. It works except I have a CASE condition on the one field that is used as the crossed data. The current query looks something like this, and only returns one detail record: select VENDOR_NUMBER as EMPLOYEE_NUM, 'TE' AS TRANSACTION_CODE, A.START_DATE AS TRIP_START_DATE, A.END_DATE AS TRIP_END_DATE, case when left(B.ZIP_CODE,2)='86' or left(B.ZIP_CODE,2)='85' then '1000' else '2000' end AS DESTINATION_CODE_1, ' ' AS DESTINATION_CODE_2, ' ' AS DESTINATION_CODE_3, ' ' AS DESTINATION_CODE_4, ' ' AS DESTINATION_CODE_5FROM TRVL_TRIP A INNER JOIN TRVL_DEST_DTL B ON (A.TRIP_ID=B.TRIP_ID)where TRVL_DEST_DTL.dest_id=1 the relational data that has five records or less needs to go into the fields DESTINATION_CODE1, DESTINATION_CODE2,DESTINATION_CODE3... As I said, I can get the following to work but can not do the CASE statement - EXECUTE crosstab1 'select trvl_trip.trip_id AS T_ID, (trvl_trip.start_date), (trvl_trip.end_date) FROM TRVL_TRIP INNER JOIN TRVL_DEST_DTL ON (TRVL_TRIP.TRIP_ID=TRVL_DEST_DTL.TRIP_ID)group by trvl_trip.trip_id,trvl_trip.start_date, trvl_trip.end_date ORDER BY TRVL_TRIP.TRIP_ID', 'count(TRVL_TRIP.TRIP_ID)','DEST_ID','','TRVL_DEST_DTL'I think I may have to do temp table and insert into those five fields unless someone has another suggestion. |
|
|
|
|
|
|
|
parent/child query into flat file