在 SQL Server 中转换数据 [英] Transform Data in SQL Server

问题描述

我今天收到了来自产品供应商的数据.

I received a data from product providers today.

AdviserName|IF-Nov16|IF-Dec16|NB-Nov16|NB-Dec16|CN-Nov16|CN-Dec16|TotalPolcy|Provider|
-----------|--------|--------|--------|--------|--------|--------|----------|--------|
John Smith |$100    |$100    |$50     |$25.50  |-$20    |-$30    |12        |ANZ     |
Adrian Ken |$200    |$250    |$1000   |$2000   |-$500   |-$700   |30        |AXA     |

如何将上面的表格转换为下面的表格?

How do i transpose the table above to below?

AdviserName|Month|Year|Status|Amount     |TotalPolicy|Provider|
-----------|-----|----|------|-----------|-----------|--------|
John Smith |Nov  |16  |IN    |$100       |12         |ANZ     |
John Smith |Dec  |16  |IN    |$100       |12         |ANZ     |
John Smith |Nov  |16  |NB    |$50        |12         |ANZ     |
John Smith |Dec  |16  |NB    |$25.50     |12         |ANZ     |
John Smith |Nov  |16  |CN    |-$20       |12         |ANZ     |
John Smith |Dec  |16  |CN    |-$30       |12         |ANZ     |
Adrian Ken |Nov  |16  |IN    |$200       |30         |AXA     |
Adrian Ken |Dec  |16  |IN    |$250       |30         |AXA     |
Adrian Ken |Nov  |16  |NB    |$1000      |30         |AXA     |
Adrian Ken |Dec  |16  |NB    |$2000      |30         |AXA     | 
Adrian Ken |Nov  |16  |CN    |-$500      |30         |AXA     |
Adrian Ken |Dec  |16  |CN    |-$700      |30         |AXA     |

目前我所做的就是将SQL中的数据下载到excel中，将数据一一转置，放回SQL中.但是大约有 4000 行数据，我放弃了.

So far what I have done is download the data from SQL to excel, transpose the data one by one, and put it back to SQL. However there are around 4000 rows of data and I'm giving up.

如何构建查询来转换这些数据?我使用的是 SQL Server 2012.

How to build query to transform these data? I'm using SQL server 2012.

谢谢.

推荐答案

如果您愿意，这里是 unpivot 语法的示例.

If you'd like, here is an example of what the unpivot syntax would look like.

create table dbo.dataTable
    (
        AdviserName varchar(100)
        , [IF-Nov16] smallmoney
        , [IF-Dec16] smallmoney
        , [NB-Nov16] smallmoney
        , [NB-Dec16] smallmoney
        , [CN-Nov16] smallmoney
        , [CN-Dec16] smallmoney
        , TotalPolcy int
        , [Provider] char(3)
    )

insert into dbo.dataTable
values ('John Smith', 100, 100, 50, 25.50, -20, -30, 12, 'ANZ')
    , ('Adrian Ken', 200, 250, 1000, 2000, -500, -700, 30, 'AXA')


select a.AdviserName
, substring(a.col_nm, 4, 3) as [Month]
, substring(a.col_nm, 7, 2) as [Year]
, substring(a.col_nm, 1, 2) as [Status]
, a.Amount
, a.TotalPolcy
, a.[Provider]
from dbo.dataTable as dt
unpivot (Amount for Col_Nm in ([IF-Nov16], [IF-Dec16], [NB-Nov16], [NB-Dec16], [CN-Nov16], [CN-Dec16])) as a

这篇关于在 SQL Server 中转换数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！