none
How to merge the rows into single line RRS feed

  • Question

  • I have a table where data will be like below.. Here all transaction starts with '03' and 4th position in all lines that is 00,01,02,04 contains same transaction's data. where as 00 have set of data,01 have some set of data, 02 have some set of data, 04 have some set of data.

    And the data is continue to repeat in same manner

    I need to combine all set of single data into same line. 

    Input: (here in input there is two transaction data i have provided)

    03 00xxxx1

    03 01yyyy2

    03 02zzzz3

    03 04aaaa4

    03 00xxxx5

    03 01yyyy6

    03 02zzzz7

    03 04aaaa8

    .

    .

    .

    .

    I need to convert like below

    Output:

    03 00xxxx1 || 03 01yyyy2 || 03 02zzzz3 || 03 04aaaa4

    03 00xxxx5 || 03 01yyyy6 || 03 02zzzz7 || 03 04aaaa8

    Also we can't hard code as the incremental number as 4, It may vary.. that is some transaction may have [00,01,02,03,04] and some have [00,01,02],[00,01,02,04]

    Can any one help to get to it?

    Friday, June 14, 2019 7:28 AM

All replies

  • You should use pivot, please review the examples in following link. 

    https://databricks.com/blog/2018/11/01/sql-pivot-converting-rows-to-columns.html

    Friday, June 14, 2019 8:09 AM
  • Hi SanthanaKumar04,

     

    Would you like this one ?

    IF OBJECT_ID('test') IS NOT NULL drop table  test 
    go
    create table test ([value] varchar(30))
    insert into test values 
    ('03 00xxxx1'),
    ('03 01yyyy2'),
    ('03 02zzzz3'),
    ('03 04aaaa4'),
    ('03 00xxxx5'),
    ('03 01yyyy6'),
    ('03 02zzzz7'),
    ('03 04aaaa8')
    
    ;with cte as (
    select [value], 
    (cast(right([value],PATINDEX('%[a-z]%',reverse([value]))-1)as int)-1)/4 PB
    from test )
    ,cte1 as (
    select *, row_number()over(partition by PB order by value) rn 
    from cte )
    select [1],[2],[3],[4] from cte1 
    pivot (
    max(value)for rn in ([1],[2],[3],[4])
    ) as p
    /*
    1                              2                              3                              4
    ------------------------------ ------------------------------ ------------------------------ ------------------------------
    03 00xxxx1                     03 01yyyy2                     03 02zzzz3                     03 04aaaa4
    03 00xxxx5                     03 01yyyy6                     03 02zzzz7                     03 04aaaa8
    */


    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 14, 2019 9:44 AM
  • Thanks friend for the valuable timely idea, however I have achieved with joins and sub queries. 
    Saturday, June 15, 2019 9:39 AM
  • Thanks friend for the valuable timely idea, however I have achieved with joins and sub queries. 
    Saturday, June 15, 2019 9:40 AM
  • Hi SanthanaKumar04,

     

    Thank you for your reply.

     

    We are glad to hear that  you have solved your issue ,please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 17, 2019 6:26 AM