question

tlenzmeier avatar image
tlenzmeier asked

How Do I transform text-based rows into columns?

Hello, I have a table that has the following columns: ProjectID, SequenceNumber, and TextField. This table is used to store project roles. Each role has a unique row. For example, if I have project number 1234, 12 (ProjectManager) as sequence number (which is actually a title), John Doe in the text field and the next row has project number 1234, 13 (ProjectCoordinator) as sequence number, Jane Doe in the text field, and so on. My instinct says that this is a prime candidate for either the pivot function in t-sql or the pivot transformation in SSIS. In t-sql I tried using ROW_NUMBER() OVER GROUP BY () and then max in the pivot function, but I still am unable to get it to work. My output should be: Project ProjectManager (Seq #12) Project Coordinator (Seq #13) 1234 John Doe Jane Does I suppose I could do a lookup in SSIS, but that seems rather tedious. Thanks. Tom
ssist-sqlpivot
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Have a read of @Jeff Moden's article - http://www.sqlservercentral.com/articles/T-SQL/63681/ - see if that helps.
1 Like 1 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.