sql 2005 行专列

/*
2013-10-08
sql 2005 行专列
*/
--1. 数据源
SELECT  '张三' AS username
UNION ALL
SELECT  '李四' AS username;

--2.XML PATH
--2.1
SELECT  username
FROM    ( SELECT    '张三' AS username
          UNION ALL
          SELECT    '李四' AS username
        ) a
FOR     XML PATH('');

----2.2 ; 拼接
SELECT  CAST(username AS VARCHAR(100)) + ';'
FROM    ( SELECT    '张三' AS username
          UNION ALL
          SELECT    '李四' AS username
        ) a
FOR     XML PATH('');
----2.3 ; STUFF XML PATH('')
SELECT  STUFF(( SELECT  ';' + username
                FROM    ( SELECT    '张三' AS username, 1 AS userNum 
                          UNION ALL
                          SELECT    '李四' AS username, 2 AS userNum 
                        ) t ORDER BY userNum DESC 
              FOR
                XML PATH('')
              ), 1, 1, '');
              
--2.4    STUFF   XML PATH('')， 去掉首个;        
SELECT  STUFF(( SELECT  ';' + username
                FROM    ( SELECT    '张三' AS username
                          UNION ALL
                          SELECT    '李四' AS username
                        ) t
              FOR
                XML PATH('')
              ), 1, 1, '');              


--3. 循环拼接法
DECLARE @value VARCHAR(MAX)           
SELECT  @value = ISNULL(@value + ';', '') + CAST(username AS VARCHAR(50))
FROM    ( SELECT    '张三' AS username
          UNION ALL
          SELECT    '李四' AS username
        ) t
ORDER BY username DESC 
SELECT  @value;

结果集如下