无需存储过程即可将字段转换为行
在 sql server 2005 的旧表中，我有一个可自定义的数据网格的设置，如下定义
TotalColumn (int, null)
Column1Name (char(12), null)
Column2Name (char(12), null)
Column3Name (char(12), null)
Column4Name (char(12), null)
Column5Name (char(12), null)
Column1Type (int, null)
Column2Type (int, null)
...
其中 TotalColumn 是一个整数 0-5，记录哪一列被总计。我不想构建一个对象模型来模仿这一点，而是想将此数据转换为按索引排序的列定义列表，就像这样，
public class LedgerColumn
{
public int Index { get; set; }
public bool IsVisible { get; set; }
public string Name { get; set; }
public LedgerColumnType ColumnType { get; set; }
public bool IsTotaled { get; set; }
}
我想在不使用存储过程的情况下这样做。过去，我使用 cte 完成数据操作，然后对数据进行透视在一个视图中。我只是不确定如何将字段分成行而不做类似的
SELECT
Column1Name,
Column1Type,
...
FROM
MyTable
UNION
SELECT
Column2Name,
...
事情告诉我必须有更好的方法，我只是不知道如何去做
In a legacy table in sql server 2005 I have settings for a customizable grid of data defined like this
TotalColumn (int, null)
Column1Name (char(12), null)
Column2Name (char(12), null)
Column3Name (char(12), null)
Column4Name (char(12), null)
Column5Name (char(12), null)
Column1Type (int, null)
Column2Type (int, null)
...
Where TotalColumn is an integer 0-5 noting which column is totaled. Instead of building an object model to mimic this, I want to convert this data into a list of column definitions, ordered by their index, like this
public class LedgerColumn
{
public int Index { get; set; }
public bool IsVisible { get; set; }
public string Name { get; set; }
public LedgerColumnType ColumnType { get; set; }
public bool IsTotaled { get; set; }
}
I want to do so without using a stored procedure. In the past I have done data manipulation using cte and then pivot the data in a view. I'm just not sure how to go about splitting the fields out into rows without doing something like
SELECT
Column1Name,
Column1Type,
...
FROM
MyTable
UNION
SELECT
Column2Name,
...
Something tells me there has to be a better way, I'm just not sure how to go about it
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
听起来您想做与枢轴相反的操作 - 取消枢轴！它们可能有点令人困惑，但我认为这就是您正在寻找的。
来自 MSDN 的 TSQL Pivot 和 Unpivot：
Sounds like you're wanting to do the opposite of a Pivot - an Unpivot! They can be somewhat confusing, but I think that's what you're looking for.
From MSDN's TSQL Pivot and Unpivot: