数据库中行列转换

SELECT *,1 AS dynum FROM dbo.SG_Gatherings WHERE CHARINDEX(',',vEmpCode)=0
UNION ALL
SELECT vmbillid,SUBSTRING(vempcode,number,CHARINDEX(',',vempcode+',',number)-number) FROM (SELECT vMBillID,vempcode FROM dbo.SG_Gatherings WHERE CHARINDEX(',',vEmpCode)>0) AS a,master..spt_values  --number 里面记录着每个明细的开始位置
WHERE  type = 'p' AND number>0 AND number <= LEN(vempcode) AND SUBSTRING(','+vempcode,number,1)=','

========================================

行转换为列

SELECT * 
INTO test2
FROM
(SELECT 'a' a,'b' b,'33333' c
UNION 
SELECT 'a','b','4444'
UNION
SELECT 'a','c','5555') AS ddd




SELECT  a , b ,
        ( STUFF(( SELECT    ';' + c
                  FROM      test2
                  WHERE     a + '_' + b = dd.a + '_' + dd.b
                  ORDER BY  a + '_' + b
                FOR
                  XML PATH('')
                ), 1, 1, '') ) AS cd
FROM    test2 AS dd
GROUP BY a,b