SQL行转列

TABLE [ dbo ] . [ TB_01 ] ( [ SN ] [ nvarchar ] ( 50 ) NULL , [ STEP_NAME ] [ nvarchar ] ( 50 ) NULL , [ SDATE ] [ datetime ] NULL ) ON [ PRIMARY ]

2.插入测试数据：

 INSERT   INTO  TB_01  VALUES ( ‘  V000001  ‘ , ‘  FQC  ‘ , ‘  2015-01-01 10:20  ‘  )
  INSERT   INTO  TB_01  VALUES ( ‘  V000001  ‘ , ‘  OQC  ‘ , ‘  2015-01-02 07:35  ‘  )
  INSERT   INTO  TB_01  VALUES ( ‘  V000002  ‘ , ‘  FQC  ‘ , ‘  2015-05-23 15:26  ‘ )

 

3.原始数据结果：

 

期望的数据格式：

V000001    FQC(2015-01-01 10:20:00),OQC(2015-01-02 07:35:00)
V000002    FQC(2015-05-23 15:26:00)

 

4.利用FOR XML PATH实现：

 SELECT   SN, LEFT (Steps, LEN (Steps) -  1 )  AS  AAAAA   FROM   (
          SELECT   SN,
        (  SELECT  Step_Name +  ‘  (  ‘  +  CONVERT ( VARCHAR ( 100 ),SDATE, 120 ) +  ‘  )  ‘  +  ‘  ,  ‘    FROM   TB_01
            WHERE  SN =  A.SN 
            FOR  XML PATH( ‘‘ ))  AS   Steps
          FROM   TB_01 A 
          GROUP   BY   SN
        ) B     

最终结果：

 

SQL行转列

标签：

查看更多关于SQL行转列的详细内容...