-2

I have the below data and I have to concatenate the long text column and make it as a single row. The challenge part is only one row has the notification number and other rows are null. You cannot group by the notification number.

enter image description here

I need the output as 2 rows

row number  Notification Number Plant   Creation Date   Language    Lineno  Tag Long Text
1           10014354914         A057    43466            EN            1     >X  aaabbbcccdddeeefffggghhhjjjkkklll
2           10014354915         A057    43466            EN            1     >X  aaabbbcccdddeeefffgggpppqqqrrrsss

I have used cursor for this. But it is taking much time.

Peak
  • 21
  • 1
  • 5
  • 1
    Show us some sample table data and the expected result - as formatted text (not images.) – jarlh Nov 07 '19 at 12:17
  • 2
    Which dbms are you using? – jarlh Nov 07 '19 at 12:17
  • please show sum sample data and output that you want..? – Arsalan Qaiser Nov 07 '19 at 12:18
  • Just click the link provided. You will see the data and I need the output as https://i.stack.imgur.com/eAMi5.png – Peak Nov 07 '19 at 12:21
  • 1
    Most people here want formatted text, not links to images. Also take a look at [mcve]. – jarlh Nov 07 '19 at 12:22
  • 1
    try to understand how markdown works, for example [here](https://rmarkdown.rstudio.com/authoring_basics.html). This language is used to write posts on stackoverflow. – Zavael Nov 07 '19 at 12:26
  • 1
    Please read the first paragraph of the [sql tag info](https://stackoverflow.com/tags/sql/info) and [edit] your question accordingly. – Zohar Peled Nov 07 '19 at 12:28
  • The images are loaded. Please check. – Peak Nov 07 '19 at 13:07
  • hi @Peak can you please tell me one thing your data sequence will be means first row is null then some value then row null then some value then row null – Arsalan Qaiser Nov 08 '19 at 05:10
  • Hi @Qaiser, I need to delete all the rows that has null in all the columns. So I will neglect it. – Peak Nov 08 '19 at 05:51
  • Select ROW_NUMBER() OVER(ORDER BY d.notificationnumber ASC) as [Row_Number], d.notificationnumber as NotificatonNumber , d.Plant , d.CreateDate , d.[Language] , d.[LineNo] , d.Tag , STUFF(( SELECT longtext as [text()] FROM testtable Where [row_number] >= d.[row_number] and [row_number] < d.MaxVal FOR XML PATH('') ), 1, 1, '') from ( Select a.* , (Select min(b.[row_number]) from testtable b where b.[lineno] is null and b.[row_number] > a.[row_number]) as Maxval from Testtable a where a.notificationnumber is not null ) d – Arsalan Qaiser Nov 08 '19 at 06:55
  • Perfect!!!!!!!!! Thanks a lot... – Peak Nov 13 '19 at 09:35

1 Answers1

0

If you are using oracle:

with data("row number", "Notification Number","Plant","Creation Date","Language","Lineno","Tag","Long Text") as (
select 1,10014354914,'A057',43466,'EN',1,'>X','aaabbbcccdddeeefffggghhhjjjkkklll' from dual
union all
select 2,10014354915,'A057',43466,'EN',1,'>X','aaabbbcccdddeeefffgggpppqqqrrrsss' from dual)
select LISTAGG("Long Text",'') within group (order by "row number") from data;

if you are using ms-sql maybe try this:

SELECT u.[Long Text]  AS [text()]
            FROM yourtable u
            ORDER BY u.[row number]
            FOR XML PATH ('')
W_O_L_F
  • 1,049
  • 1
  • 9
  • 16