Query Help

[Anonymous]

Can anyone give me some help with this query? I’m trying to concatenate multiple rows from a SQL Server table into a single row, for every key.

Example Table

Key               Comments

Order1          This is the initial comment record.

Order1          This is the second comment.

Order1          Third comment.

Order2          Here is a comment.

Order2          Final comment.

Desired Result

Key               Comments

Order1          This is the initial comment record.; This is the second comment.; Third comment.

Order2          Here is a comment.; Final comment.

[its_anandrjs · Accepted solution]

Use Concat key word

See the below load script

===================================

Source:

LOAD * INLINE [

    Key,               Comments

    Order1,          This is the initial comment record.

    Order1,          This is the second comment.

    Order1,          Third comment.

    Order2,          Here is a comment.

    Order2,          Final comment.

];

FinalTable:

LOAD

Key,             

Concat(Comments,';') as Comments

Resident Source

Group By Key;

Drop Table Source;

[#3]

Try this;

Load Key, Concat(Comments,';') as Comments

From Table

Group By Key;

Thanks

[Anonymous]

  Well, that was ridiculously simple. My problem was thinking in SQL instead of QlikView. Thanks!

[its_anandrjs]

Same as Concat key word you can use in the SQL but i believe you are not able to add symbol

SELECT CONCAT ( Field1, Field2) AS Result;

[anbu1984]

In Sql, you can try this

Select Stuff(

        (

        Select ', ' + T2.Comments  // Add a comma (,) before each value

        From Table As T2

        Where T2.Key= T1.Key

        For Xml Path(''), type // Select it as XML

        ).value('.', 'nvarchar(max)'), 1, 2, '')  // This is done to remove the first character (,) from the result

From Table As T1

Group By T1.Key