如何只用C＃中的冗余数据从逗号数据库中获取值 [英] How to fetch value from database in comma only for redundant data in C#

问题描述

你好编码器，



我有一张桌子我需要列



DPS技巧

XA

XB

Y c

YD



如何获取数据



DPS技能

XA，B

YC，D



并将它们存储在c＃中的DataTable中我搜索的代码很少，但这与我的要求不符，任何帮助都将不胜感激。



先谢谢。



我的尝试：



直到现在我试图

Hi coder,

I have a table in which I have to columns

DPS Skill
X A
X B
Y c
Y D

how to get the data in

DPS Skill
X A,B
Y C,D

And store them in DataTable in c# I search few code but that doesn't match my requirement any help will be appreciated.

Thanks in Advance.

What I have tried:

Till now I tried to

StringBuilder skills = new StringBuilder();
                    for (int i = 0; i < dtSkills.Rows.Count; i++)
                    {
                        for (int j = 0; j < dtSkills.Rows.Count; j++)
                        {
                            if (dtSkills.Rows[i]["DemandPrimarySkill"].ToString().Equals(dtSkills.Rows[j]["DemandPrimarySkill"].ToString()))
                            {
                                if (dtSkills.Rows[i]["Skill"].ToString().ToUpper().Trim().Equals(dtSkills.Rows[j]["Skill"].ToString().ToUpper().Trim()))
                                {
                                    if (!String.IsNullOrEmpty(skills.ToString()))
                                    {
                                        skills.Append(",");
                                    }
                                    skills.Append(dtSkills.Rows[j]["Skill"].ToString());
                                }
                            }
                            else
                            {
                                break;
                            }

                        }

           }

我有这个dtSkill我拥有所有DPS并且与DPS相关我有技能我尝试用字符串构建器附加技能但没有得到我想要的。

in this I have dtSkill in which I have all the DPS and related to that DPS I have skills I try to append skill with string builder but not got what I desire.

推荐答案

你可以使用实现这样的结果STUFF（）

检查以下示例 -

You can achieve such result using STUFF()
Check following example-

DECLARE @tbl AS TABLE(DPS VARCHAR(10),Skill VARCHAR(10))
INSERT INTO @tbl
SELECT 'X','A'
UNION ALL
SELECT 'X','B'
UNION ALL
SELECT 'Y','C'
UNION ALL
SELECT 'Y','D'


SELECT DPS,
STUFF((SELECT ', ' + A.Skill FROM @tbl A
WHERE A.DPS=B.DPS FOR XML PATH('')),1,1,'') AS [Skill]
From @tbl B
GROUP BY DPS

希望，它有帮助：）

Hope, it helps :)

试试这个，应该帮到你解决你的请求。



DemoTable：

Try this, should help you to solve your request.

DemoTable:

CREATE TABLE Demo 
(
  DPS CHAR(30),
  SKILL CHAR(30)
);

测试数据：

Test Data:

INSERT INTO Demo (DPS, SKILL) VALUES
('X', 'A'),
('X', 'B'),
('Y', 'C'),
('Y', 'D');

测试SQL：

Test SQL:

SELECT  DPS,
        SKILLVALS= STUFF((SELECT RTRIM(SKILL) + ','
                          FROM Demo D2
                          WHERE D2.DPS = D1.DPS
                          ORDER BY D2.SKILL
                          FOR XML PATH('')), 1, 0, '')
FROM Demo D1
GROUP BY D1.DPS
ORDER BY D1.DPSS

测试结果：

Test Result:

DPS  SKILLVALS
---  ---------
X    A,B,
Y    C,D,

小缺点：SKILLVALS末尾的'，'。



不，我没找到通过研究MSDN文档来解决这个问题:)

在这里您可以找到以下信息： SQL Server Grouped Concatenation - SQLPerformance.com [ ^ ]，在那里搜索FOR XML PATH。



我希望它有所帮助。 />


感谢 Richard Deeming - 专业资料 [ ^ ]，请参阅他对上述解决方案的评论。

Small drawback: The ',' at the end of SKILLVALS.

And no, I did not find this solution by studying the MSDN documentation :)
Here you find the information: SQL Server Grouped Concatenation - SQLPerformance.com[^], search for 'FOR XML PATH' there.

I hope it helps.

Thanks to Richard Deeming - Professional Profile[^], see his comment to the above solution.

SELECT DPS,
       SKILLVALS= STUFF((SELECT ',' + RTRIM(SKILL) 
                         FROM Demo D2
                         WHERE D2.DPS = D1.DPS
                         ORDER BY D2.SKILL
                         FOR XML PATH('')), 1, 1, '')
FROM Demo D1
GROUP BY D1.DPS
ORDER BY D1.DPS

最后结果没有前导或尾随'，'：

Finally the result without leading or trailing ',':

DPS  SKILLVALS
---  ---------
X    A,B
Y    C,D

这篇关于如何只用C＃中的冗余数据从逗号数据库中获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！