请教各位在SQL2005中用取列值数组的SQL表达式应该如何编写。以下表达式在数据库中是不正确的,请教正确的表达式该如何写。
select
产品类型,array(select 产品编号 from 产品销售表 ) as 产品编号数组
from 产品销售表麻烦各位帮忙解决,谢谢了。sql数据库
select
产品类型,array(select 产品编号 from 产品销售表 ) as 产品编号数组
from 产品销售表麻烦各位帮忙解决,谢谢了。sql数据库
例如数据表Table1
A列 B列
1 a
1 b
1 c查询后的结果是
A列 B列
1 a,b,c
-- Author :DBA_Huangzj(发粪涂墙)
-- Date :2013-01-05 21:22:08
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
-- Jun 17 2011 00:57:23
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[Table1]
if object_id('[Table1]') is not null drop table [Table1]
go
create table [Table1]([A列] int,[B列] varchar(1))
insert [Table1]
select 1,'a' union all
select 1,'b' union all
select 1,'c'
--------------开始查询--------------------------select a.[A列],
stuff((select ','+[B列] from [Table1] b
where b.[A列]=a.[A列]
for xml path('')),1,1,'') 'B列'
from [Table1] a
group by a.[A列]
----------------结果----------------------------
/*
(3 行受影响)
A列 B列
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 a,b,c(1 行受影响)
*/