table aname id class amounts
a 1 001 120
a 1 006 45.6
a 1 008 50.2
b 2 003 56.3
c 3 009 36.2
...能不能查询的结果
a 1 001 120 006 45.6 008 50.2
b 2 003 56.3
c 3 009 36.2
...我要做的报表的效果 是按name 分类后所有的class都跟在后面,每个name的class个数不等,我没有想到用什么方法做,请各位大虾帮忙帮忙.....
a 1 001 120
a 1 006 45.6
a 1 008 50.2
b 2 003 56.3
c 3 009 36.2
...能不能查询的结果
a 1 001 120 006 45.6 008 50.2
b 2 003 56.3
c 3 009 36.2
...我要做的报表的效果 是按name 分类后所有的class都跟在后面,每个name的class个数不等,我没有想到用什么方法做,请各位大虾帮忙帮忙.....
b 2 003 56.3
c 3 009 36.2其中
001 120 006 45.6 008 50.2
是在一列中显示呢,还是分成六列显示?
=================================================
dulei115(前途无亮)
if object_id('test') is not null drop table test
select 1 as ptd_no, 10 as Qty, '货号' as [Name], 'F100' as dsc_rem
into test
union select 1, 10, '客号', 'B100'
union select 2, 20, '货号', 'C100'
union select 2, 20, '客号', 'D100'
--------------------------------------------
declare @s varchar(800)
set @s = 'select * from (select ''订单号'' as ptd_no, ''数量'' as Qty'
select @s = @s + ', ''' + a.[Name] + ''' as ' + a.[Name]
from (select distinct [name] from test) a
set @s = @s + ' union select cast(ptd_no as varchar), cast(Qty as varchar)'
select @s = @s + ', min(case [name] when ''' + a.[Name]
+ ''' then dsc_rem end)'
from (select distinct [name] from test) a
set @s = @s + ' from test group by ptd_no, Qty) a '
+ 'order by case isnumeric(ptd_no) when 1 then ptd_no else 0 end'
exec(@s)
/*
ptd_no Qty 货号 客号
------------------------------ ------------------------------ ---- ----
订单号 数量 货号 客号
1 10 F100 B100
2 20 C100 D100*/
--------------------------------------------
drop table test
CREATE TABLE tb([name] varchar(10), [id] int, [class] varchar(10), amounts DECIMAL(8, 2))
INSERT INTO tb
SELECT 'a', 1, '001', 120
UNION ALL SELECT 'a', 1, '006', 45.6
UNION ALL SELECT 'a', 1, '008', 50.2
UNION ALL SELECT 'b', 2, '003', 56.3
UNION ALL SELECT 'c', 3, '009', 36.2
GOCREATE FUNCTION GetList(@ID INT)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @RE VARCHAR(1000)
SET @RE = ''
SELECT @RE = @RE + class + ' ' + CAST(amounts AS CHAR(10)) + ' ' FROM tb WHERE ID = @ID
RETURN LEFT(@RE, LEN(@RE) - 1)
END
GOSELECT DISTINCT [name], [id], dbo.GetList(ID) FROM tb
DROP FUNCTION GetList
DROP TABLE tb
select 'XPU01U' as VendorID, '001' as ItemClass7,100 as Amounts
union select 'XPU01U','002',300
union select 'XPU01U','009',600
union select 'XPU01H','999',999
---------------------------------------------------------declare @s varchar(800)
set @s = ' select * from (select ''VendorID'' as VendorID'
select @s = @s + ', ''' + a.ItemClass7 + ''' as '+ a.ItemClass7
from (select ItemClass7 from test) a
set @s = @s + ' union select cast(VendorID as varchar) '
select @s = @s + ',min(case ItemClass7 when ''' + a.ItemClass7
+ ''' then Amounts end)'
from (select ItemClass7 from test) a
set @s = @s + ' from test group by VendorID) a '
+ ' order by VendorID'
-- + ' order by case isnumeric(VendorID) when 1 then VendorID else 0 end'
exec(@s)
我的怎么老报错, 帮我看看
包错信息Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '009'.