'How to transpose rows into columns in and map values associated to them

Here's what I've tried:

SELECT *
FROM
    (SELECT 
         dbo.LC1.[tableNumber],
         [keyhash], [Key1], [Key2], [Data2]
     FROM 
         [dbo].[LC1]) AS SourceTable 
PIVOT
    (AVG([Data2]) FOR [Key2] 
        IN ([Doc A], [Doc B], [Doc C], [Doc D], [Doc E])
    ) AS PivotTable;

I've tried to replicate an example but I'm having no luck. Say I have a table like this:

+--------+-------------------------------------------------+----------------+-------+
| Region | Key2                                            | Subregion      | Data2 |
+--------+-------------------------------------------------+----------------+-------+
| 703    | Building Per $100 Of Limit Of Ins               | NULL           | 0.125 |
+--------+-------------------------------------------------+----------------+-------+
| 703    | Business Personal Prop Per $100 Of Limit Of Ins | NULL           | 0.125 |
+--------+-------------------------------------------------+----------------+-------+
| 703    | Lessors Liability Per $100 Of Limit Of Ins      | Fountain Hills | 0.125 |
+--------+-------------------------------------------------+----------------+-------+
| 703    | Lessors Liability Per $100 Of Limit Of Ins      | NULL           | 0.125 |
+--------+-------------------------------------------------+----------------+-------+

What I want to do is take the values in Key2 and make them columns of their own. As you can see some values repeat in the column, so they'd need to be distinct. Also, the values in the Data2 column should still line up with the corresponding Key2 values, like so:

+--------+--------------------------------------------+----------------+-------------------------------------------------+-----------------------------------+
| Region | Lessors Liability Per $100 Of Limit Of Ins | Subregion      | Business Personal Prop Per $100 Of Limit Of Ins | Building Per $100 Of Limit Of Ins |
+--------+--------------------------------------------+----------------+-------------------------------------------------+-----------------------------------+
| 703    | 0.125                                      | NULL           | 0.125                                           | 0.125                             |
+--------+--------------------------------------------+----------------+-------------------------------------------------+-----------------------------------+
| 703    | 0.125                                      | Fountain Hills | NULL                                            | NULL                              |
+--------+--------------------------------------------+----------------+-------------------------------------------------+-----------------------------------+
sqlsql-server


Solution 1:[1]

you can use group by to achieve this particular result.

select region, 
   data2 as [Lessors Liability Per $100 Of Limit Of Ins], 
   subregion, 
   data2 as [Business Personal Prop Per $100 Of Limit Of Ins],
   data2 as [Building Per $100 Of Limit Of Ins ]
from sourcetable
group by subregion, region, data2

getting more complex just requires being aware of which data you're actually selecting, and how you're naming it.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions