CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Harish G
Harish G

Reputation: 11

SQL Server condition based concatenation of multiple rows

Consider a table holding data like below,

COL1|   COL2|   active_flag
----|   ----|   ------------
1   |   A   |   Y
1   |   B   |   Y
1   |   C   |   Y
1   |   D   |   N
1   |   E   |   N
2   |   M   |   Y
2   |   N   |   Y
2   |   O   |   N
2   |   P   |   Y
2   |   Q   |   Y

and I require the output like below.

COL1|   COL2
----|   -----
1   |   ABC
1   |   D
1   |   E
2   |   MN
2   |   O
2   |   PQ

How to achieve this in SQL Server 2012

Upvotes: 0

Views: 252

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176144

First you should add column to id to get stable sort. Then you could calculate each group and then concatenate using FOR XML:

WITH cte AS(
    SELECT *, 
       CASE WHEN 
         LAG(active_flag) OVER(ORDER BY id)  <> active_flag
         OR LAG(active_flag) OVER(ORDER BY id) = 'N' AND active_flag = 'N' THEN 1
         ELSE 0
     b  END as l
    FROM t
), cte2 AS (
    SELECT *, SUM(l) OVER(ORDER BY id) AS grp
    FROM cte
)
SELECT DISTINCT col1, (SELECT '' + col2
              FROM cte2
              WHERE grp = c.grp
              ORDER BY id
              FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)') AS col2
FROM cte2 c;

Rextester demo

Output:

enter image description here

Warning!

ORDER BY 1/0 or ORDER BY (SELECT 1) do not provide stable sort.

Upvotes: 2

Related Questions