一张表结构如下
ID QID AID RID
1 1 3 1001
1 2 4 1001
1 3 1 1001
1 4 2 1001
2 1 3 1002
2 2 4 1002
2 3 1 1002
3 4 1 1002
......这是一个‘调查活动’的表其中 ID 代表调查的主ID, QID代表的是问题的ID,AID代表的是答案的ID,RID代表的是用户的ID我现在想导出数据 如下形式 1 2 3 4 5 6 .....(QID)
1001(用户ID) 3 4 1 2 ..........(AID)
1002 3 4 1 1 ..........(AID)
谢谢各位了~~
ID QID AID RID
1 1 3 1001
1 2 4 1001
1 3 1 1001
1 4 2 1001
2 1 3 1002
2 2 4 1002
2 3 1 1002
3 4 1 1002
......这是一个‘调查活动’的表其中 ID 代表调查的主ID, QID代表的是问题的ID,AID代表的是答案的ID,RID代表的是用户的ID我现在想导出数据 如下形式 1 2 3 4 5 6 .....(QID)
1001(用户ID) 3 4 1 2 ..........(AID)
1002 3 4 1 1 ..........(AID)
谢谢各位了~~
insert into a values(1,1,3,1001)
insert into a values(1,2,4,1001)
insert into a values(1,3,1,1001)
insert into a values(1,4,2,1001)
insert into a values(2,1,3,1002)
insert into a values(2,2,4,1002)
insert into a values(2,3,1,1002)
insert into a values(4,4,1,1002)
declare @sql nvarchar(4000)set @sql =' select B.RID'
select @sql = @Sql +', (SELECT TOP 1 AID FROM A WHERE RID = B.RID AND QID = '
+ CAST( CC.QID AS VARCHAR(10)) +') AS '''+
CAST( CC.QID AS VARCHAR(10)) +'''' FROM (SELECT Distinct Qid FROM A) AS CCSET @Sql = @Sql +' FROM
(select distinct rid from a) AS B '
exec(@sql)
select @sql = @Sql +', (SELECT TOP 1 AID FROM A WHERE RID = B.RID AND QID = '
+ CAST( CC.QID AS VARCHAR(10)) +') AS '''+
CAST( CC.QID AS VARCHAR(10)) +'''' FROM (SELECT Distinct Qid FROM A) AS CCSET @Sql = @Sql +' FROM
(select distinct rid from a) AS B '
exec(@sql)