1

Here is an example of data:

TABLE: DRIVERS

ID    | FNAME | LNAME
------+-------+------
WR558 | WILL  | RIKER
WW123 | WALT  | WHITE

TABLE: ACCIDENTS

DRIVER | ACCIDENT_NBR | ACCI_DATE  | ACCI_CITY | ACCI_ST
-------+--------------+------------+-----------+--------
WW123  | 4-7777       | 2014-01-01 | Chicago   | IL
WW123  | 4-7782       | 2014-01-03 | Houston   | TX
WW123  | 4-7988       | 2014-01-15 | El Paso   | NM

There could be any number of accidents listed for this driver in this table or there could be none

What i need to see is this:

ID    | FNAME | LNAME  | ACCIDENT1_NBR | ACCI1_DATE | ACCI1_CITY | ACCI1_ST | ACCIDENT2_NBR | ACCI2_DATE | ACCI2_CITY | ACCI2_ST | ACCIDENT3_NBR | ACCI3_DATE | ACCI3_CITY | ACCI3_ST | ... | ACCIDENT10_NBR | ACCI10_DATE | ACCI10_CITY | ACCI10_ST
------+-------+--------+---------------+------------+------------+----------+---------------+
WR558 | WILL  |  RIKER |               |            |            |          |               | ...
WW123 | WALT  |  WHITE | 4-7777        | 2014-01-01 | Chicago    | IL       | 4-7782        | ...

I need to pull the driver info and the 10 most recent accidents (if any) onto this one line. I'm unsure if I need to use a PIVOT table or a FETCH. Either way, I'm not sure how to iterate into the columns as needed.

Anyone's assistance is greatly appreciated!

I'm using SQL Server 2012 and this will ultimately get pulled into a .XLS SSRS file. The pipes I included are only for display and will not be part of the final result

Alvie
  • 23
  • 6

1 Answers1

0

You should use PIVOT for this as ugly as it will be it is the correct choice if coding a C# solution is not viable.

Here is an example: Simple Pivot sample

Community
  • 1
  • 1
T McKeown
  • 12,971
  • 1
  • 25
  • 32