如何处理这个返回的数据记录.顶者有分
zip count name
-----------------------
9450125LIA
9450114SBE
9450112VSBE
945027LIA
945022SBE
945022VSBE
我要把它变成下面这样的,再绑定到GridView上
zip LIA SBE VSBE
-----------------------
94501 25 14 12
94502 7 2 2
[解决办法]
我顶!
[解决办法]
交叉表，你看看。。
sqlserver帮助里有。。。
[解决办法]
create table a(zip varchar(10),count int,name varchar(10)) insert into a values( '94501 ',25, 'LIA ')insert into a values(94501,14, 'SBE ')insert into a values(94501,12, 'VSBE ')insert into a values(94502,7, 'LIA ')insert into a values(94502,2, 'SBE ')insert into a values(94502,2, 'VSBE ') select * from adeclare @str varchar(2000)set @str = 'select zip 'select @str = @str + ',sum(case name when ' ' ' + name + ' ' ' then count else 0 end ) as ' + name from (select distinct name from a) as aaset @str = @str + ' from a group by zip 'exec(@str)
[解决办法]
帮顶
[解决办法]
楼上的把动态表都搬出来了。
其实我认为只要把数据集取出来按照自己的格式转换成XML再转换到DataSet中就行了，以前做过，现在找不到代码了。顶一个
[解决办法]
select alltable.zip,
LIA=(select ta.count from table as ta where ta.name= 'lia ' and ta.zip = alltable.zip),
SBE=(select tb.count from table as tb where tb.name= 'SBE ' and tb.zip = alltable.zip),
VSBE=(select tc.count from table as tc where tc.name= 'VSBE ' and tc.zip = alltable.zip)
from table as alltable where alltable.zip in
(
select zip from table group by zip
)
[解决办法]
楼上的方法不错，支持一下。
在zip LIA SBE VSBE中，我估计字段数量还是不定的
在做商店时，商品的属性是不定的，所以可能得用这种数据结构
[解决办法]
come here to study and uping
[解决办法]
CREATE TABLE [Test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[zip] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[count] [numeric](18, 0) NULL ,
[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [test] ([zip],[count],[name]) values (N '94501 ',25,N 'LID ')
INSERT INTO [test] ([zip],[count],[name]) values (N '94501 ',14,N 'SBE ')
INSERT INTO [test] ([zip],[count],[name]) values (N '94501 ',12,N 'VSBE ')
INSERT INTO [test] ([zip],[count],[name]) values (N '94502 ',7,N 'LID ')
INSERT INTO [test] ([zip],[count],[name]) values (N '94502 ',2,N 'SBE ')
INSERT INTO [test] ([zip],[count],[name]) values (N '94502 ',2,N 'VSBE ')
Go
select zip,sum(case name when 'LID ' then [count] else 0 end) as 'LID ',
sum(case name when 'SBE ' then [count] else 0 end) as 'SBE ',
sum(case name when 'VSBE ' then [count] else 0 end) as 'VSBE '
from test
group by zip
drop table test
