SQL行轉列的問題! 用語句select a.SN,a.Process_id,b.Data_id,b.MeasureData from cr_test a left join cr_data b on a.test_id = b.test_id order by a.sn,b.data_id,a.process_id得到記錄如下 sn process_id data_id measuredata 12770006 1 1 1.34 12770006 1 2 0.46 12770006 1 3 9.82 14061916 2 1 5.5 14061916 2 2 4.36 12770006 2 1 6.43 12770006 2 2 0.12 12770006 2 3 6.73 14061916 3 1 3.4 14061916 3 2 1.1 但是這不符合客戶的要求,他們的要求是 sn process_id field1 field2 field3 field412770006 1 1.34 0.46 9.82 Null14061916 2 5.5 4.36 Null Null12770006 2 6.43 0.12 6.73 Null14061916 3 3.4 1.1 Null Null 就是在原來查詢記錄的基礎上,把measuredata按照data_id的順序橫向排列,但是需要注意的是不同的sn可能measuredata的數目不同.如12770006有3個,14061916隻有2個,因此在field3,fiels4處置為Null.但最長到field4. 方法一(L欄位數目不確定)CREATE TABLE tb(sn varchar(20),process_id int,data_id int,measuredata numeric(9,2))INSERT tb SELECT '12770006',1,1,1.34UNION ALL SELECT '12770006',1,2,046UNION ALL SELECT '12770006',1,3,9.82 UNION ALL SELECT '14061916',2,1,5.5UNION ALL SELECT '14061916',2,2,4.36UNION ALL SELECT '12770006',2,1,6.43UNION ALL SELECT '12770006',2,2,0.12 UNION ALL SELECT '12770006',2,3,6.37 UNION ALL SELECT '14061916',3,1,3.4UNION ALL SELECT '14061916',3,2,1.1 --查詢處理DECLARE @s nvarchar(4000)--交叉報表處理代碼頭SET @s='SELECT sn,process_id'--產生列記錄水平顯示的處理代碼拼接(處理Item列)SELECT @s=@s+','+QUOTENAME(data_id)+N'=SUM(CASE data_id WHEN '+QUOTENAME(data_id,N'''')+N' THEN measuredata END)'FROM tbGROUP BY data_id --拼接交叉報表處理尾部,並且執行拼接後的動態SQL語句EXEC(@s+N'FROM tbGROUP BY sn,process_id') 方法二(L欄位數目不確定)declare @s varchar(2000)set @s='select sn,process_id'select @s=@s+',field'+ltrim(tb.data_id)+'=sum(case when tb.data_id='''+ltrim(tb.data_id)+''' then tb.measuredata else 0 end)'from tbgroup by tb.data_idset @s=@s+' from tb group by sn,process_id'exec(@s) 方法三(L欄位數目固定)Select a.SN,a.Process_id, [field1]=max(case when data_id=1 then measuredata end), [field2]=max(case when data_id=2 then measuredata end), [field3]=max(case when data_id=3 then measuredata end), [field4]=max(case when data_id=4 then measuredata end)From tb aGroup By a.SN,a.Process_idorder by a.SN,a.Process_id
