如何获得彼此相邻的列值 [英] how to get a columns values next to each other

问题描述

男生/女生

我在使用SQL时遇到问题.有点难以解释，但要尝试

我有一个查询返回的结果如示例所示:

Hi guys/girls

I am having a problem with SQL. Its kinda hard to explain but going to try

I have a query returning results as shown in the example:

name,surname,telno,result
john,joe,011,45
john,joe,011,55
john,joe,011,50
piet,pee,011,30
piet,pee,011,44
piet,pee,011,45
piet,pee,011,45

我想得到这样的结果

and I want to get the results like so

john,joe,011,45,55,50
piet,pee,011,40,44,45,45

我遇到的问题是每人的结果数并不总是相同的数字，例如john有3个结果，而piet有4个结果，依此类推.

请注意，，"是用于新列的

任何人都可以提供帮助吗?

The problem I am having is that the number of result per person is not always the same number, example john has 3 results , and piet has 4 results and so on.

Please note that the "," is for a new column

Can anyone assist with this please ?

推荐答案

我认为您能做的最好的就是将结果作为xml字符串返回，就像这样

The best I think you can do is return the results as an xml string, something like this

SELECT        name, surname, STUFF
                             ((SELECT        ',' + result AS Result
                                 FROM            results AS t1
                                 WHERE        (t2.name = name) FOR XML path('')), 1, 1, '') AS Results
FROM            results AS t2
GROUP BY name, surname
ORDER BY name

.

我在名为results的表上对此进行了测试，因此请替换您自己的表名.

希望这对您有帮助

.

I tested this on a table called results, so substitute your own table name.

Hope this helps

AFAIK，您不能使用可变的列数来做到这一点:所有返回的列数都相同，且固定.
不过，您可以为空列返回DBNULL，在这种情况下，它与 MSDN示例 [ ^ ]

AFAIK, you can''t do it with variable number of columns: all the returns have the same, fixed column count.
You could return a DBNULL for empty columns though, in which case it is very similar to the MSDN example[^]

您可以将代码编写为在数据透视概念中选择表值.
在Sql Server中，我们可以编写查询以将行转换为列.
您可以编写数据透视表查询.

You can write a code to select your table values in pivot concept.
In Sql server we can write a query to convert a row to column.
you can write a pivot table query.

这篇关于如何获得彼此相邻的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！