How to combine multiple rows data until next row value is not null in SQL Server

Question

How to combine multiple rows data until next row value is not null in SQL Server

kishor9329

SSC Enthusiast

Points: 173
More actions
August 13, 2020 at 3:55 pm

#3780302

A
B
C
NULL
F
R
NULL
R
T
G
Expected output:
ABC
FR
RTG

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Your result set is dependant upon an order yet your source data has no column defining this order. Is there such a column?

If so, can you provide a script to generate a table or CTE which has the data elements necessary for creating your output?

Thanks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kishor9329 SSC Enthusiast Points: 173 More actions · Answer 2

Not having a order is the problem, is it possible to give the order to these data like 1 to the
rows(1,2,3) untill null then assign 2 to rows(5,6) and so on?

CREATE TABLE T_REST(ROW_NUMBER INT, VALUE VARCHAR(100))

INSERT INTO T_REST(ROW_NUMBER, VALUE) VALUES(1,'A');

INSERT INTO T_REST(ROW_NUMBER, VALUE) VALUES(2,'B');

INSERT INTO T_REST(ROW_NUMBER, VALUE) VALUES(3,'C');

INSERT INTO T_REST(ROW_NUMBER, VALUE) VALUES(4,NULL);

INSERT INTO T_REST(ROW_NUMBER, VALUE) VALUES(5,'F');

INSERT INTO T_REST(ROW_NUMBER, VALUE) VALUES(6,'R');

INSERT INTO T_REST(ROW_NUMBER, VALUE) VALUES(7,NULL);

INSERT INTO T_REST(ROW_NUMBER, VALUE) VALUES(8,'R');

INSERT INTO T_REST(ROW_NUMBER, VALUE) VALUES(9,'T');

INSERT INTO T_REST(ROW_NUMBER, VALUE) VALUES(10,'G');

INSERT INTO T_REST(ROW_NUMBER, VALUE) VALUES(11,NULL);?

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

This should work for you

WITH CTE1 AS (
SELECT [ROW_NUMBER],VALUE,
   CASE WHEN LAG(VALUE) OVER(ORDER BY [ROW_NUMBER]) IS NULL THEN 1 ELSE 0 END AS ISSTART
FROM T_REST
),
CTE2 AS (
SELECT [ROW_NUMBER],VALUE,
       SUM(ISSTART) OVER(ORDER BY [ROW_NUMBER]) AS GRP
FROM CTE1
)
SELECT STRING_AGG(VALUE,'') WITHIN GROUP (ORDER BY [ROW_NUMBER]) AS VALS
FROM CTE2
WHERE VALUE IS NOT NULL
GROUP BY GRP;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537