将行更改为列 [英] Change rows to columns

问题描述

我想将行更改为列。这就是我现在拥有的：



NAME |年|收入|费用|雇员

COMPANY-1 | 2017年| €30000 | €15000 | 10

COMPANY-1 | 2016 | €50000 | €10000 | 8

COMPANY-2 | 2017年| €20000 | €5000 | 20

COMPANY-2 | 2016 | €80000 | €95000 | 5



这就是我想要的：



NAME | INCOME_2017 | EXPENSE_2017 | INCOME_2016 | EXPENSE_2016 | EMLOYES_2017 | .....

COMPANY-1 | €30000 | €15000 | €50000 | €10000 | 10

COMPANY-2 | €20000 | €5000 | €80000 | €95000 | 20



有人知道我该怎么做吗？



我试过的：



一切，我复制并编辑了很多PIVOT和UNPIVOT脚本，但是没有用。

I want to change my rows to a column. This is what I have now:

NAME | YEAR | INCOME | EXPENSE | EMPLOYES
COMPANY-1 | 2017 | €30000 | €15000 | 10
COMPANY-1 | 2016 | €50000 | €10000 | 8
COMPANY-2 | 2017 | €20000 | €5000 | 20
COMPANY-2 | 2016 | €80000 | €95000 | 5

And this is what I want:

NAME | INCOME_2017 | EXPENSE_2017 | INCOME_2016 | EXPENSE_2016 |EMLOYES_2017 | .....
COMPANY-1 | €30000 | €15000 | €50000 | €10000 | 10
COMPANY-2 | €20000 | €5000 | €80000 | €95000 | 20

Has someone an idea how i can do this?

What I have tried:

Everything, I have copied and edit a lot of PIVOT and UNPIVOT script, but that didn't work..

推荐答案

尝试多个CTE和JOIN，例如

Try multiple CTEs and JOIN, e.g.

WITH CTE2017 AS
(
   SELECT NAME, YEAR AS YEAR_2017, INCOME AS INCOME_2017, EXPENSE AS EXPENSE_2017, EMPLOYES AS EMPLOYES_2017 FROM tablename WHERE YEAR='2017'   
),
CTE2016 AS
(
   SELECT NAME, YEAR AS YEAR_2016, INCOME AS INCOME_2016, EXPENSE AS EXPENSE_2016, EMPLOYES AS EMPLOYES_2016 FROM tablename WHERE YEAR='2016'   
)
SELECT CTE2017.NAME, YEAR_2017, INCOME_2017, EXPENSE_2017, EMPLOYES_2017, YEAR_2016, INCOME_2016, EXPENSE_2016, EMPLOYES_2016 FROM CTE2017 JOIN CTE2016 ON CTE2017.NAME=CTE2016.NAME

这篇关于将行更改为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！