SQLTeam.com | Weblogs | Forums

Pivoting categorical variables



This is my first SQL query.

I have four tables that I want to use.

Table 1: DAT_ACCESSORY has INSPN_ID and ACC_ID and looks like this ( Every car inspected gets an inspected ID. ACC_ID is accessory ID. Each of those numbers specifies the accessory found in the car). Ex: 53 is AM/FM radio

701 53
701 69
702 71
702 74
703 83
703 84
703 87

Table 2: DAT_INPN has INSPN_ID and many other variables and looks like this:

INSPN_ID DAM_Count etc....
701 12
702 38
703 33
704 0
705 12
706 0
707 5


53 A/C
52 Airbag
60 Navigation
78 CD player

Table 4: DAT_vehicle and has VEH_VIN ( which is the vehicle vin) and other information about the vin ( like car model, year, series)...

I want to have a table this:

VIN Accessory 1 Accessory 2 Accessory 3.... Accessory n DAM_COUNT
34324 Yes No No Yes 12
32342 No Yes No No 23

So, first I have all the accessory descriptions in table 3. I have a total of 392 accessories. I need to put those as a row. Then, for each vin I need to specify whether that accessory was present or not. This is where the yes and no come into play. The way to know which accessories are in a specific vin is from table 2 which ties the INSPN_ID ( the inspection id) and specifies all the accessories that are for the ID.


Would you please post a query that extracts the data from these tables? Then we can show you how to pivot it. (FOR XML might be the best option here.)