sql交叉表

Question

select id,[ValidID],[UserName],[TheAnswer] into #tmp from D_VoteQuestionAnswer where [TypeID]=1 and MainTileID not in(6,12,18) order by id asc
declare @sql varchar(max) set @sql = ''
select @sql = @sql + '[' + ValidID + '] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = ''' + ValidID + '''),' from #tmp group by [ValidID]
set @sql = left(@sql,len(@sql)-1) 
set @sql = 'select a.[UserName] as 姓名,' + @sql  + ' from #tmp a' + ' group by a.[UserName]'
exec(@sql)
drop table #tmp查出来的东西会自动按ValidID这个排序显示,为什么order by id asc没有起到作用
怎样才能让他可以按order by id asc的显示出来了

Answer 1

因为你的动态语句中没有order by id asc.
而且你涉及到的查询中也没有id列.

Answer 2

set @sql = 'select a.id , a.[UserName] as 姓名,' + @sql + ' from #tmp a' + ' group by  a.id , a.[UserName] order by a.id'如果不对,
最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281

Answer 3

在你执行 exec @sql 之前,最好用 select @sql 查看一下你的动态语句是什么样儿的.

Answer 4

 id    UserName  ValidID          TheAnswer
9631 aaa 87899079 常规地面公交车
9632 aaa 59765200 购物、休闲、娱乐
9633 bbb 87899079 轨道交通
9634 bbb 59765200 70%-90%（经常）
9635 ccc 87899079 不太好的
9636 ccc 59765200 一般表这是样的，想实现
姓名    1                    2
aaa 常规地面公交车    购物、休闲、娱乐
bbb 轨道交通         70%-90%（经常）
ccc 不太好的           一般
就是想实现这种效果

Answer 5

生成的语句是
select a.[UserName] as 姓名,[00070547] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '00070547'),[01542708] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '01542708'),[03269580] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '03269580'),[07540484] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '07540484'),[10116945] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '10116945'),[12332887] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '12332887'),[12756739] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '12756739'),[12804429] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '12804429'),[14333542] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '14333542'),[15535745] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '15535745'),[18223814] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '18223814'),[18688093] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '18688093'),[19889998] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '19889998'),[20573331] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '20573331'),[21957332] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '21957332'),[22119326] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '22119326'),[22201091] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '22201091'),[23147818] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '23147818'),[23565020] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '23565020'),[24048366] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '24048366'),[25829278] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '25829278'),[26118951] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '26118951'),[27585982] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '27585982'),[30177293] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '30177293'),[31557148] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '31557148'),[33421974] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '33421974'),[36152915] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '36152915'),[36601809] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '36601809'),[37851443] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '37851443'),[38422701] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '38422701'),[38473750] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '38473750'),[38724389] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '38724389'),[39040143] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '39040143'),[39210935] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '39210935'),[39983645] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '39983645'),[40861696] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '40861696'),[42606654] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '42606654'),[44083444] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '44083444'),[48102045] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '48102045'),[48435374] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '48435374'),[49300562] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '49300562'),[51042118] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '51042118'),[51450116] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '51450116'),[54471081] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '54471081'),[56693110] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '56693110'),[56891332] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '56891332'),[57631166] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '57631166'),[59107464] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '59107464'),[59765200] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '59765200'),[61658422] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '61658422'),[63328009] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '63328009'),[64055905] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '64055905'),[64172725] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '64172725'),[64348299] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '64348299'),[65927585] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '65927585'),[67484186] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '67484186'),[67484532] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '67484532'),[69390625] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '69390625'),[69844068] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '69844068'),[72761996] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '72761996'),[73388393] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '73388393'),[76341200] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '76341200'),[76616340] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '76616340'),[79074108] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '79074108'),[80696530] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '80696530'),[81488029] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '81488029'),[82046218] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '82046218'),[83713899] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '83713899'),[85665595] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '85665595'),[87595008] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '87595008'),[87899079] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '87899079'),[88428188] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '88428188'),[93866435] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '93866435'),[97925690] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '97925690'),[99100311] = (select TheAnswer from #tmp b where b.[UserName] = a.[UserName] and b.[ValidID] = '99100311') from #tmp a group by a.[UserName]

Answer 6

create table tb(id int,UserName varchar(10),ValidID varchar(10),TheAnswer varchar(20))
insert into tb values(9631 ,'aaa', '87899079', '常规地面公交车')
insert into tb values(9632 ,'aaa', '59765200', '购物、休闲、娱乐')
insert into tb values(9633 ,'bbb', '87899079', '轨道交通')
insert into tb values(9634 ,'bbb', '59765200', '70%-90%（经常）')
insert into tb values(9635 ,'ccc', '87899079', '不太好的')
insert into tb values(9636 ,'ccc', '59765200', '一般')
go--如果你确定每个username最多两个值，则使用静态SQL即可。
select UserName,
       max(case px when 1 then TheAnswer else '' end) TheAnswer_1,
       max(case px when 2 then TheAnswer else '' end) TheAnswer_2
from
(
  select t.* , px = (select count(1) from tb where UserName = t.UserName and id < t.id) + 1 from tb t
) m
group by UserName
/*
UserName   TheAnswer_1          TheAnswer_2          
---------- -------------------- -------------------- 
aaa        常规地面公交车              购物、休闲、娱乐
bbb        轨道交通                 70%-90%（经常）
ccc        不太好的                 一般（所影响的行数为 3 行）
*/--如果你不能确定每个username的值，则使用动态SQL。
declare @sql varchar(8000)
set @sql = 'select UserName '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then TheAnswer else '''' end) [TheAnswer_' + cast(px as varchar) + ']'
from (select distinct px from (select t.* , px = (select count(1) from tb where UserName = t.UserName and id < t.id) + 1 from tb t) b) as a
set @sql = @sql + ' from (select t.* , px = (select count(1) from tb where UserName = t.UserName and id < t.id) + 1 from tb t)m group by UserName'
exec(@sql) 
/*
UserName   TheAnswer_1          TheAnswer_2          
---------- -------------------- -------------------- 
aaa        常规地面公交车              购物、休闲、娱乐
bbb        轨道交通                 70%-90%（经常）
ccc        不太好的                 一般
*/drop table tb

Answer 7

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[UserName] varchar(3),[ValidID] int,[TheAnswer] varchar(16))
insert [tb]
select 9631,'aaa',87899079,'常规地面公交车' union all
select 9632,'aaa',59765200,'购物、休闲、娱乐' union all
select 9633,'bbb',87899079,'轨道交通' union all
select 9634,'bbb',59765200,'70%-90%(经常)' union all
select 9635,'ccc',87899079,'不太好的' union all
select 9636,'ccc',59765200,'一般'
 
---查询---
declare @sql varchar(8000)
select 
  @sql=isnull(@sql+',','')
  +'max(case when num='+ltrim(num)+' then theanswer end) as ['+ltrim(num)+']'
from
(select distinct num=(select count(1) from tb where username=t.username and id<=t.id) from tb t) tt
set
 @sql='select username,'
 +@sql+' from (select *,num=(select count(1) from tb where username=t.username and id<=t.id) from tb t)tt group by username'
--print @sqlexec(@sql)---结果---
username 1                2
-------- ---------------- ----------------
aaa      常规地面公交车          购物、休闲、娱乐
bbb      轨道交通             70%-90%(经常)
ccc      不太好的             一般
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)

Answer 8

我7楼是sql 2000中用子查询的办法.
下面是sql 2005中用row_number的方法.
create table tb(id int,UserName varchar(10),ValidID varchar(10),TheAnswer nvarchar(20))
insert into tb values(9631 ,'aaa', '87899079', N'常规地面公交车')
insert into tb values(9632 ,'aaa', '59765200', N'购物、休闲、娱乐')
insert into tb values(9633 ,'bbb', '87899079', N'轨道交通')
insert into tb values(9634 ,'bbb', '59765200', N'70%-90%（经常）')
insert into tb values(9635 ,'ccc', '87899079', N'不太好的')
insert into tb values(9636 ,'ccc', '59765200', N'一般')
go--如果你确定每个username最多两个值，则使用静态SQL即可。
select UserName,
       max(case px when 1 then TheAnswer else '' end) TheAnswer_1,
       max(case px when 2 then TheAnswer else '' end) TheAnswer_2
from
(
  select t.* , px = row_number() over(partition by UserName order by id) from tb t
) m
group by UserName
/*
UserName   TheAnswer_1          TheAnswer_2
---------- -------------------- --------------------
aaa        常规地面公交车              购物、休闲、娱乐
bbb        轨道交通                 70%-90%（经常）
ccc        不太好的                 一般(3 行受影响)
*/--如果你不能确定每个username的值，则使用动态SQL。
declare @sql varchar(8000)
set @sql = 'select UserName '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then TheAnswer else '''' end) [TheAnswer_' + cast(px as varchar) + ']'
from (select distinct px from (select t.* , px = row_number() over(partition by UserName order by id) from tb t) b) as a
set @sql = @sql + ' from (select t.* , px = row_number() over(partition by UserName order by id) from tb t)m group by UserName'
exec(@sql) 
/*
UserName   TheAnswer_1          TheAnswer_2
---------- -------------------- --------------------
aaa        常规地面公交车              购物、休闲、娱乐
bbb        轨道交通                 70%-90%（经常）
ccc        不太好的                 一般(3 行受影响)*/drop table tb