如何将列转换为sql server中的行

Please consider this table:

请考虑此表:

ID         Page          Line           C01          C02          C03        
---------------------------------------------------------------------
1          122            11             1            0            1
1          123            11             1            1            1
1          124            12             0            0            0
1          125            16             1            0            1
1          127            11             0            1            0

I want to convert this table to this one:

我想将此表转换为此表:

ID         Page          Line           City         Value
-----------------------------------------------------------
1          122            11            C01            1           
1          122            11            C02            0  
1          122            11            C03            1  
1          123            11            C01            1  
1          123            11            C02            1  
1          123            11            C03            1  
...

How I can do this in appropriate way?

我怎么能以适当的方式做到这一点?

5 个解决方案

#1

Use UNPIVOT. Try something like:

使用UNPIVOT。尝试以下方法:

SELECT ID, Page, Line, City, Value
FROM SourceTable
UNPIVOT
   (Value FOR City IN 
      (C01, C02, C03)
)AS unpvt;

Where 'SourceTable' is your source table name. (Note: I can't test this at the moment, so it may not be exactly right.)

'SourceTable'是您的源表名称。 (注意:我现在无法测试,所以它可能不完全正确。)

Full details here: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

详细信息请访问:http://msdn.microsoft.com/en-us/library/ms177410(v = sql.105).aspx

#2

Pivot & UnPivot will solve the issue :)

Pivot&UnPivot将解决问题:)

follow the links:

点击链接:

#3

You can achieve this by doing something like this in Linq to sql

您可以通过在Linq to sql中执行此类操作来实现此目的

Is Unpivot (Not Pivot) functionality available in Linq to SQL? How?

Linq to SQL中是否提供Unpivot(非Pivot)功能?怎么样?

#4

Do a self-join, once for each 1+Nth column. And create a cartesian table.

为每个第1 + N列进行一次自连接。并创建一个笛卡尔表。

Self-join is joining the same table to itself. Match it by id, page and line.

自联接将同一个表连接到自身。按ID,页面和行匹配。

#5

Below query should do your requirement.

以下查询应该满足您的要求。

SELECT ID, PAGE, LINE, "C01", C01 FROM TABLE
UNION 
SELECT ID, PAGE, LINE, "C02", C02 FROM TABLE
UNION
SELECT ID, PAGE, LINE, "C03", C03 FROM TABLE

#1