Combine rows into one row with line break by querying with XML

  • Hi,

    I have a table like this:

    Create table Tbl_Test01

    (

    Name nvarchar(20),

    Item nvarchar(30)

    )

    ;

    insert into Tbl_Test01

    values

    ('Car','Honda2016'),

    ('Car','Honda2017'),

    ('Car','Honda2018'),

    ('Tool','Dwwalt2016'),

    ('Tool','Dwwalt2017'),

    ('Tool','Dwwalt2018'),

    ('Tool','Dwwalt2019')

    select *

    from Tbl_Test01

    I'd like to have the value of the column Name show up once and the column Item to be concartenated and seperated with the line break. Plase see attached for the desired result.

    Here is what I have worked on so far but it's not quite what I wanted. I think the line break it's either Char(13) or Char(10) but XML will display wierd characters.

    SELECT distinct Name, STUFF((

    SELECT ' ' + CHAR(10) + Item

    FROM Tbl_Test01

    FOR XML PATH('')

    ), 1, 2, '')

    AS Item

    From Tbl_Test01 a

    Please HELP! Thank you very much in advance!

  • This is a presentation issue and should be left to the presentation layer.

    I am not seeing any strange characters when I run it. XML will entitize CHAR(13) (
), so I use CHAR(10) instead, which you've already done.

    If you are viewing this in the SSMS grid, it will replace the line feeds with spaces. If you are viewing it in text output, it will not align subsequent rows. (This is why it's recommended to do this in the presentation layer.)

    Drew

    Edited to get correct encoding display.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • You can try the following query but not sure whether it is the most efficient way of doing it.

    SELECT CASE WHEN RowRank = 1 THEN Name ELSE '' END AS Name, Item

    FROM

    (

    select Name, Item, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Item) AS RowRank

    from #TempTable

    ) AS DT

  • shogunSQL (7/12/2016)


    Hi,

    I have a table like this:

    Create table Tbl_Test01

    (

    Name nvarchar(20),

    Item nvarchar(30)

    )

    ;

    insert into Tbl_Test01

    values

    ('Car','Honda2016'),

    ('Car','Honda2017'),

    ('Car','Honda2018'),

    ('Tool','Dwwalt2016'),

    ('Tool','Dwwalt2017'),

    ('Tool','Dwwalt2018'),

    ('Tool','Dwwalt2019')

    select *

    from Tbl_Test01

    I'd like to have the value of the column Name show up once and the column Item to be concartenated and seperated with the line break. Plase see attached for the desired result.

    Here is what I have worked on so far but it's not quite what I wanted. I think the line break it's either Char(13) or Char(10) but XML will display wierd characters.

    SELECT distinct Name, STUFF((

    SELECT ' ' + CHAR(10) + Item

    FROM Tbl_Test01

    FOR XML PATH('')

    ), 1, 2, '')

    AS Item

    From Tbl_Test01 a

    Please HELP! Thank you very much in advance!

    Try this:

    SELECT

    Name,

    STUFF

    ((

    SELECT ' ' + CHAR(10) + Item

    FROM Tbl_Test01

    FOR XML PATH('')

    ),1,2,'')

    AS Item

    From Tbl_Test01 a

    GROUP BY name;

    if that does not work, try this:

    SELECT

    Name,

    STUFF

    ((

    SELECT ' ' + CHAR(10) + Item

    FROM Tbl_Test01

    FOR XML PATH(''), TYPE

    ).value('.[1]','varchar(100)'),1,2,'')

    AS Item

    From Tbl_Test01 a

    GROUP BY name

    See this article[/url] to better understand the technique you are using.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Thank you very much All for your efforts.

    I think I got it now. By replaceing ® sign where Char(10) is, The ® sign is used as a trick for our systems to read as new line seperator. Maybe for many other systems too but someone need to test that.

  • shogunSQL (7/12/2016)


    Thank you very much All for your efforts.

    I think I got it now. By replaceing ® sign where Char(10) is, The ® sign is used as a trick for our systems to read as new line seperator. Maybe for many other systems too but someone need to test that.

    You should just use the TYPE option of XML to de-entitize.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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