Concat all column values in sql
Posted on Tuesday, November 22, 2011
|
No Comments
Table Name WHArchieveOut
|
GoodReceiveID
|
CartonNo
|
|
1
|
1,2,3
|
|
2
|
1,5,7
|
|
1
|
4,5
|
|
1
|
6,7
|
|
2
|
2,3
|
SELECT Main.GoodReceiveID,
LEFT(Main.WHArchieveOut, Len(Main.WHArchieveOut)
- 1) AS 'CartonList'
FROM (SELECT
DISTINCT ST2.GoodReceiveID,
(SELECT
ST1.CartonNo + ',' AS [text()]
FROM dbo.WHArchieveOut ST1
WHERE
ST1.GoodReceiveID = ST2.GoodReceiveID
ORDER BY ST1.GoodReceiveID
FOR XML PATH(''))
[WHArchieveOut]
FROM
dbo.WHArchieveOut ST2)[Main] where Main.GoodReceiveID
= '1'
OutPut
|
GoodReceiveID
|
'CartonList'
|
|
1
|
1,2,3,4,5,6,7
|
