I have a database with over twenty tables and need to transpose only two columns from different tables to display as rows. The tables are linked by a fields in each table. At the moment I have:SELECT TECH_Id, DT_Description, TECH_Value_StringFROM pub_car.dbo.NVDTechnical INNER JOINpub_car .dbo.NVDDictionaryTechnical ON pub_car.dbo.NVDTechnical.TECH_TechCode = pub_car.dbo.NVDDictionaryTechnical.DT_TechCode WHERE (pub_car.dbo.NVDTechnical.TECH_Id = 30557)Which Gives : Tech_Id DT_Description Tech_Value_String 30557 Insurance Group 1 12 30557 Insurance Group 2 D 30557 Minimum Kerbweight 1220 30557 Length 4223 What I can't seem to acheive is:Tech_Id Insurance Group 1 Insurance Group 2 Minimum Kerbweight Length30557 12 D 1 220 4223 Any help appreciated as I am new to sql syntax. I don't even know if this is possible. I don't seem to be able to work out if any of the examples apply. Thanks again in advance.
Just thought I'd try to simplify the query request:SELECT Col_1 ,Col_3 , Col_4FROM Table AINNER JOINTable BON Table A.Col_1 = Table B. Col_1WHERE (Table A.Col_1 = 4046) Which gives me: Col_1 Col_3 Col_4 4046 Field_1 Val_1 4046 Field_2 Val_2 4046 Field_3 Val_3 I need it transposing or pivoting so I get: Field_1 Field_2 Field_3 Val_1 Val_2 Val_3 Hope this is clearer. Any help appreciated. I can't seem to get my head around the pivot with an inner join
Check BOL under "Using PIVOT and UNPIVOT", more specifically the "Complex PIVOT example". It doesn't really matter how many tables are in the FROM part, although it would help if you start adding the table alias to all column references in your query (even if the BOL example doesn't have them either).
