sql - 如何编写查询以在不指定每个列名称的情况下用逗号分隔每个列

Question

如何写一个查询，让table1的每一列都用逗号隔开不包括每个列名

+------+-----+-----------+-------------+
| eno  |dno  | ename     | job_type    |
+------+-----+-----------+-------------+
| 101  | 1   | sam       | manager     |
| 102  | 2   | ash       | teacher     |
| 103  | 3   | rohan     | clerk       |
| 104  | 4   | sohan     | peon        |
| 105  | 5   | mohan     | guar        |
+------+-----+------------+------------+

我想这样输出

101,1,sam,manager      
102,2,ash,teacher

其他都是一样的

Answer 1

在 CROSS APPLY(或两个)的帮助下，一些 XML 和 STUFF()

A word of caution: This approach will EXCLUDE NULL values

Declare @YourTable table (eno int,dno int, ename varchar(25),job_type varchar(25))
Insert Into @YourTable values
(101,1,'sam'  , 'manager'),
(102,2,'ash'  , 'teacher'),
(103,3,'rohan', 'clerk'  ),
(104,4,'sohan', 'peon'   ),
(105,5,'mohan', 'guar'   ) 


Select C.*
 From  @YourTable A
 Cross Apply (Select XMLData=cast((Select A.* for XML Raw) as xml)) B
 Cross Apply ( 
                Select String=Stuff((Select ',' +Value 
                  From (
                        Select Value  = attr.value('.','varchar(max)') 
                         From  XMLData.nodes('/row') as A(r)
                         Cross Apply A.r.nodes('./@*') AS B(attr)
                       ) X
                  For XML Path ('')),1,1,'')
             ) C

返回

String
101,1,sam,manager
102,2,ash,teacher
103,3,rohan,clerk
104,4,sohan,peon
105,5,mohan,guar