SQL Server Performance

Transpose Only Two Columns into Rows

Discussion in 'Getting Started' started by Steve74, Aug 7, 2008.

  1. Steve74 New Member

    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
    .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.
  2. Steve74 New Member

    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
  3. Adriaan New Member

    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).

Share This Page