sql一个行列转换

[newnazi]

已知两个表 testResult 和testResultDetail 分别代表测试结果和测试结果明细 希望将明细数据和 输出在一行即行列转换
输出如下：
id，BarCode， TestDateTime，Result，TestName1，Test1，TestName2，Test2，TestName3，Test3
1 123456 2015-05-31 20:00:10 A1 1.50 A2 10.50 A3 22
testResult:
列名：
1)id
2）BarCode
3) TestDateTime
4) Result

Insert into valeus(BarCode,TestDateTime,Result)values('123456','2015-05-31 20:00:10','P')

testResultDetail
1)id
2) BarCode
3) TestDateTime
4) TestName
5) Result

Insert into (BarCode,TestDateTime,TestName,Result)values('123456','2015-05-31 20:00:10','A1','1.50')
Insert into (BarCode,TestDateTime,TestName,Result)values('123456','2015-05-31 20:00:10','A2','10.50')
Insert into (BarCode,TestDateTime,TestName,Result)values('123456','2015-05-31 20:00:10','A3','22')

[kenlewis]

如果是在数据库里显示，基本上很难。但是如果你要用于报表显示，就可以用现成的工具了，交叉报表。

[godbless_zf]

如果你的TestName是固定的则：

 SELECT ID,BarCode,TestDateTime,'A1' AS TestName1,[A1] AS Result1,'A2' AS TestName2,[A2] AS Result2,'A3' AS TestName3,[A3] AS Result3 
 FROM 
 (SELECT A.ID,A.BarCode,A.TestDateTime,B.TestName,B.Result FROM #testResult A INNER JOIN #testResultDetail B ON A.BarCode=B.BarCode) AS T1
 PIVOT (MAX(T1.Result) FOR T1.TestName IN ([A1],[A2],[A3]) ) AS T2

输出结果： ID BarCode TestDateTime TestName1 Result1 TestName2 Result2 TestName3 Result3 0 123456 2015-05-31 20:00:10.000 A1 1.50 A2 10.50 A3 22

[newnazi]

引用 2 楼 godbless_zf 的回复:

如果你的TestName是固定的则：

 SELECT ID,BarCode,TestDateTime,'A1' AS TestName1,[A1] AS Result1,'A2' AS TestName2,[A2] AS Result2,'A3' AS TestName3,[A3] AS Result3 
 FROM 
 (SELECT A.ID,A.BarCode,A.TestDateTime,B.TestName,B.Result FROM #testResult A INNER JOIN #testResultDetail B ON A.BarCode=B.BarCode) AS T1
 PIVOT (MAX(T1.Result) FOR T1.TestName IN ([A1],[A2],[A3]) ) AS T2

输出结果： ID BarCode TestDateTime TestName1 Result1 TestName2 Result2 TestName3 Result3 0 123456 2015-05-31 20:00:10.000 A1 1.50 A2 10.50 A3 22

运行报错啊！！！！！！！！差错中 顶一下

[發糞塗牆]

你的是2000吧

[newnazi]

引用 4 楼 DBA_Huangzj 的回复:

你的是2000吧

引用 4 楼 DBA_Huangzj 的回复:

你的是2000吧

2005

[發糞塗牆]

报什么错？？

[唐诗三百首]

create table testResult
(id int identity(1,1),BarCode varchar(10),TestDateTime varchar(30),Result varchar(5))
 
insert into testResult(BarCode,TestDateTime,Result)values('123456','2015-05-31 20:00:10','P')

create table testResultDetail
(id int identity(1,1),BarCode varchar(10),TestDateTime varchar(30),TestName varchar(5),Result varchar(10))

insert into testResultDetail(BarCode,TestDateTime,TestName,Result)values('123456','2015-05-31 20:00:10','A1','1.50')
insert into testResultDetail(BarCode,TestDateTime,TestName,Result)values('123456','2015-05-31 20:00:10','A2','10.50')
insert into testResultDetail(BarCode,TestDateTime,TestName,Result)values('123456','2015-05-31 20:00:10','A3','22')


declare @tsql varchar(6000)

select @tsql=isnull(@tsql+',','')
            +' max(case when b.rn='+rtrim(number)+' then b.TestName else '''' end) ''TestName'+rtrim(number)+''', '
            +' max(case when b.rn='+rtrim(number)+' then b.Result else '''' end) ''Test'+rtrim(number)+''' '
 from master.dbo.spt_values
 where type='P' and number>=1
 and number<=
 (select max(c) 
  from (select count(1) 'c' from testResultDetail group by BarCode) t)

select @tsql=
'select a.id,a.BarCode,a.TestDateTime,a.Result,'+@tsql
+' from testResult a
   inner join (select *,row_number() over(partition by BarCode order by id) ''rn''
               from testResultDetail) b on a.BarCode=b.BarCode
   group by a.id,a.BarCode,a.TestDateTime,a.Result '

exec(@tsql)

/*
id          BarCode    TestDateTime                   Result TestName1 Test1 TestName2 Test2 TestName3 Test3
----------- ---------- ------------------------------ ------ --------- ----- --------- ----- --------- -----
1           123456     2015-05-31 20:00:10            P      A1        1.50  A2        10.50 A3        22

(1 row(s) affected)
*/