SQL行转列的问题

 

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 field4

12770006              1                1.34     0.46        9.82    Null

14061916              2                5.5      4.36        Null    Null

12770006              2                6.43     0.12        6.73    Null

14061916              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.34

UNION ALL SELECT '12770006',1,2,046

UNION ALL SELECT '12770006',1,3,9.82

UNION ALL SELECT '14061916',2,1,5.5

UNION ALL SELECT '14061916',2,2,4.36

UNION ALL SELECT '12770006',2,1,6.43

UNION ALL SELECT '12770006',2,2,0.12

UNION ALL SELECT '12770006',2,3,6.37

UNION ALL SELECT '14061916',3,1,3.4

UNION 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 tb

GROUP BY data_id

 

-- 拼接交叉报表处理尾部,并且执行拼接后的动态SQL语句

EXEC(@s+N'

FROM tb

GROUP 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 tb

group by tb.data_id

set @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 a

Group By a.SN,a.Process_id

order by a.SN,a.Process_id