一列，多个字段分成不同的列.. [英] One column, multiple fields into separate columns..

问题描述

我确信这很简单，但我是SQL的新手并且很难解决这个问题。所以，任何帮助将不胜感激。



我有一个名为Fees的表，在这个费用表中，我有一个名为MI_Code的字段，它有多个值（EX， GI，AP，C1）。一个人可以有多个MI代码，但是当我运行我的查询时，该个人会显示多行，其中包含不同的mi代码值。我希望将结果视为一行中具有多个值的列中的个体。



个人 MI_Code1 MI_Code2

Individual1 EX GI



希望这是有道理的。感谢您的时间!!



我的尝试：



我试过谷歌，SQL论坛，书籍。我真的不确定如何用它来找到我正在寻找的答案。谢谢！

I'm sure this is pretty simple, but I'm new to SQL and having a hard time trying to figure this out. So, any help will be greatly appreciated.

I have a table called Fees, in this fees table, I have a field called MI_Code that have multiple values (EX, GI, AP, C1). An individual can have multiple MI codes, but when I run my query, the individual shows up in multiple rows with the different mi code values. I would like to see the results as the individual in one row with the multiple values in columns.

Individual MI_Code1 MI_Code2
Individual1 EX GI

Hope this makes sense. Thanks for your time!!

What I have tried:

I've tried Google, SQL Forums, books. I'm not really sure how to phrase it to find the answers I'm looking for. Thanks!

推荐答案

您可以做的最好的事情是将此字符串拆分为适当的值，并将每个值存储在表格的单独列中原始字符串在其自己的列中，以供参考（如果需要）。在将记录插入表中时执行此操作。



这样，在查询时可以获得最佳性能，因为SQL引擎不必执行解析操作查询中的每一行都会大大减慢。

The best possible thing you can do is to break up this string into the appropriate values and store each value in a separate column in the table along with the original string in its own column for a reference if required. You do this as you insert the records into the table.

That way you get the best performance when querying since the SQL engine won't have to do a parsing operation on each and every row in the query slowing things down considerably.

根据您的评论试试这个

Try this, based on your comments

select individual, [1] as code1, [2] as code2, [3] as code3, [4] as code4
from
(
  select  individual,MI_Code,
         row_number() over (partition by individual order by MI_Code) rn
  from mytable
) s
pivot (min (MI_Code) for rn in ([1], [2], [3], [4])) pvt

这篇关于一列，多个字段分成不同的列..的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！