将多行数据转换为一列 [英] Getting data from multiple rows into one column
问题描述
我的表格值如下：
PersonID Degree
55 MD
55 Phd
55 RN
60 MD
60 Phd
我需要创建一个查询，它会给我这样的输出：
PersonID学位
55医学博士，博士，RN
60医学博士，博士
任何想法
bika（ae*****@gmail.com）写道：我有一张表，其价值如下：
PersonID学位
55 MD
55 Phd
55 RN
60 MD
60 Phd
我需要创建一个查询，它会给我这样的输出：
PersonID学位
55 MD，Phd，RN
60 MD，Phd
如果您使用的是SQL 2000，则必须运行游标。没有定义
方法来生成此结果集。 （有一些未定义的方法可能会工作，但我不建议依赖。）
如果您使用的是SQL 2005，这是可能的感谢改进的XML支持。
我从SQL Server开发人员那里得到了这个例子：
选择CustomerID，
substring（ OrdIdList，1，datalength（OrdIdList）/ 2 - 1）
- 从列表中删除最后一个''，''
来自
客户c交叉申请
（选择转换（nvarchar（30），OrderID）+''，''作为[text（）]
来自Orders o
其中o.CustomerID = c.CustomerID
由o.OrderID订购
为xml路径（'''））为Dummy（OrdIdList）< br $> b $ b go
我还没有真正掌握它是如何工作的，但它确实有效。 :-)
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
SQL Server 2005联机丛书
http://www.microsoft.com/technet/pro...ads/books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons/books.mspx
这里是'另一个：
如果你事先知道不同类型的学位是什么，你可以使用这个查询：
选择personid，
Min（当学位=''Md''然后学位结束时的情况）为''Md''，
Min（Case当Degree =''Phd''然后学位结束）为''Phd''，
Min（Cas e当Degree =''Rn''则度数结束时）为''Rn''
来自学位
group by PersonId
如果你事先不知道数据库中可以达到的学位，你可以使用光标来生成''min（case ... end）as ..，''parts on
苍蝇：
声明@DegName varchar（50）
声明@Sql nvarchar（4000）
声明c光标FAST_FORWARD for
从学位顺序中选择不同程度
open c
从c获取到@DegName
set @Sql =''select personid''
而@@ Fetch_Status = 0
开始
设置@Sql = @Sql +''，Min（案例当度=''''''+ @DegName +''''''然后
degree end）as'''''+ @DegName +'''''''
从c获取下一个@degName
end
关闭c
deallocate c
设置@Sql = @Sql +''来自Degrees group by PersonId''
print @sql
exec（@sql）
Erland，我实际上是从你那里学到了这个动态的sql！
希望这会有所帮助，
Gert -Jan
这个问题在数据库新闻组中出现了很多。如果你还记得，规则1
是没有重复的群体。因此，创建一个创建重复组的查询
违背SQL模型。
要以最SQL方式执行此操作，请创建一个像
CREATE TABLE PersonDegrees（
PersonID int，
IsRN char（1），
是MD char（1），
IsPHD char（1），
....
....
IsLawyer Char（1） ））
哪里是... =''Y''或''N''
这看起来像一个repeatng组，但事实并非如此。
通过这种方式，您可以进行以下查询：
告诉我的是MD，PHD，而非律师的人。
您可以从原来的M：M表中轻松填充此表格。
Rich
" bika" < AE ***** @ gmail.com>在消息中写道
news：11 ********************* @ z14g2000cwz.googlegro ups.com ...我的表格值如下：
PersonID Degree
55 MD
55 Phd
55 RN
60 MD
60 Phd
我需要创建一个查询，它会给我这样的输出：
PersonID学位
55 MD，Phd，RN
60 MD，Phd
任何想法
I have a table that has values as follows:
PersonID Degree
55 MD
55 Phd
55 RN
60 MD
60 Phd
I need a create a query that will give me output like this:
PersonID Degree
55 MD, Phd, RN
60 MD, Phd
Any ideas
bika (ae*****@gmail.com) writes:I have a table that has values as follows:
PersonID Degree
55 MD
55 Phd
55 RN
60 MD
60 Phd
I need a create a query that will give me output like this:
PersonID Degree
55 MD, Phd, RN
60 MD, Phd
If you are on SQL 2000, you will have to run a cursor. There is no defined
way to produce this result set. (There are some undefined ways which may
work, but I would not recommend to rely on.)
If you are on SQL 2005, this is possible thanks to the improved XML support.
I got this example from an SQL Server developer:
select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last '','' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + '','' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('''')) as Dummy(OrdIdList)
go
I have not really grasped how it works, but it works. :-)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Here''s another one:
If you know in advance what the different types of degrees are going to
be you can use this query:
select personid,
Min(Case when Degree = ''Md'' then degree end) as ''Md'',
Min(Case when Degree = ''Phd'' then degree end) as ''Phd'',
Min(Case when Degree = ''Rn'' then degree end) as ''Rn''
from Degrees
group by PersonId
If you dont know in advance what degrees you can expect in the db, you
can use a cursor to produce the ''min(case ... end) as .., '' parts on
the fly:
declare @DegName varchar(50)
declare @Sql nvarchar(4000)
declare c cursor FAST_FORWARD for
select distinct degree from degrees order by degree
open c
fetch next from c into @DegName
set @Sql = ''select personid ''
while @@Fetch_Status = 0
begin
set @Sql = @Sql + '', Min(Case when Degree = '''''' + @DegName + '''''' then
degree end) as '''''' + @DegName + '''''' ''
fetch next from c into @DegName
end
close c
deallocate c
set @Sql = @Sql + '' from Degrees group by PersonId''
print @sql
exec (@sql)
Erland, i actually learned this dynamic sql from you!
Hope this helps,
Gert-Jan
This problem pops up a lot in database newsgroups. If you remember, rule 1
is "no repeating groups". So to create a query that creates repeating groups
goes against the SQL model.
To do this in the most SQL way, Create a table like
CREATE TABLE PersonDegrees(
PersonID int,
IsRN char(1),
Is MD char(1),
IsPHD char(1),
....
....
IsLawyer Char(1))
Where Is...= ''Y'' or ''N''
This looks likes a repeatng group, but it is not.
This way you can do queries like:
Show me people that are MDs, PHDs, and not Lawyers.
You can easily populate this table from your original M:M table.
Rich
"bika" <ae*****@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...I have a table that has values as follows:
PersonID Degree
55 MD
55 Phd
55 RN
60 MD
60 Phd
I need a create a query that will give me output like this:
PersonID Degree
55 MD, Phd, RN
60 MD, Phd
Any ideas
这篇关于将多行数据转换为一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
