Combine multiple rows into one row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Combine multiple rows into one row

Author

Topic

tking1
Starting Member

22 Posts

Posted - 2010-09-20 : 11:33:55

Hi,

I have a ProjectID, Version, and a Comments column.

ID1 comment 3
ID1 comment 2
ID1 comment 1
ID2 comment 2
ID2 comment 1
ID3 comment 3
ID3 comment 2
ID3 comment 1

I need to combine all comments for each ProjectID into one string delimited by ***, with the comments for each ID sorted by Version in desc order. So the results would look like this:

ID1 comment 3 *** comment 2 *** comment 1
ID2 comment 2 *** comment 1
ID3 comment 3 *** comment 2 *** comment 1

Can anyone share ideas on how to construct the query?

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-20 : 11:38:39

which SQL version are you using?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-20 : 11:45:41

If it's 2005 or better (you are posting in a 2005 forum) then try


SELECT
    dlpid.[projectID]
    , LEFT(coms.[comments], LEN(coms.[comments]) -3 ) AS [Comments]
FROM
    (
    SELECT DISTINCT [projectID] AS [projectID] FROM <table>
    )
    AS dlpid

    OUTER APPLY (
        SELECT [comment] + '***'
        FROM <table> AS t
        WHERE t.[projectID] = dlpid.[projectID]
        ORDER BY [version]
        FOR XML PATH('')
        )
        AS coms ([comments])

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

tking1
Starting Member

22 Posts

Posted - 2010-09-20 : 12:05:20

Wow, that is just excellent (and fast)!! Thanks so much

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-20 : 12:12:52

It's a bit of a hack.

This is a presentation issue -- it's generally better to do this in your application layer.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-20 : 13:36:11

quote:

It's a bit of a hack.

This is a presentation issue -- it's generally better to do this in your application layer.

Charlie

If application uses paging, it may have problems.

About the query, I think in this case, using subquery is better performance.

tking1
Starting Member

22 Posts

Posted - 2010-09-20 : 13:58:06

Works for my purpose. All I'm doing is creating a notes log that I can import into the datasheet view of a SharePoint list, so I can turn off versioning without losing historical notes.

Thanks again for the help!

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-20 : 14:41:25

quote:
Originally posted by tking1

Works for my purpose. All I'm doing is creating a notes log that I can import into the datasheet view of a SharePoint list, so I can turn off versioning without losing historical notes.

Thanks again for the help!

Ah see..pronoun trouble...

ummm..I mean...

You want to keep history?

Create history tables and a trigger to move the modified data to them

Piece of Kelp

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources