-1

Looking for a way to convert columns to rows in sql server.

I have a table with the columns below:

[ID] [Action] [Note] [Resolution]

Here is what I want to get as the result with the columns: [ID] [Notes] And the result values will be:

'1' 'Action1'
'1' 'Note1'
'1' 'Resolution1'
'2' 'Action2'
'2' 'Note2'
'2' 'Note2.1'
'2' 'Resolution2' etc

Any ideas how I could do this in T-SQL? Also for the note field there could be multiple entries. Thanks!

jpw
  • 44,361
  • 6
  • 66
  • 86
rory83
  • 47
  • 7
  • What you want is know as `unpivot`. There are several questions here explaining it, as well as the manual. – jpw Jan 07 '16 at 20:26
  • Especially the "multiple Note entries" are not clear to me: please provide realistic sample data and expected output – Shnugo Jan 07 '16 at 20:28
  • Thanks for your replies. For the notes, in the source table an [id] could be in the table with the same action and resolution but different data for the notes. So multiple rows with the same ID, action and resolution. So I am looking to convert them to rows grouped by id with Actions, then any notes and then the resolution. – rory83 Jan 07 '16 at 20:39
  • Possible duplicate of [SQL Server : Columns to Rows](http://stackoverflow.com/questions/18026236/sql-server-columns-to-rows) – Tab Alleman Jan 07 '16 at 20:49

1 Answers1

1

Assuming your source table and data looks like this:

-- select * from t:
ID  Action  Note    Resolution
--- ------- ------- -----------
1   Action1 Note1   Resolution1
2   Action2 Note2   Resolution2
2   Action2 Note2.1 Resolution2

This query:

select distinct id, notes
from (select * from t) as source
unpivot (notes for ids in ([action], [note], [resolution])
) as unpivotted_table

will produce this result:

id  notes
--- ------
1   Action1
1   Note1
1   Resolution1
2   Action2
2   Note2
2   Note2.1
2   Resolution2

which looks a lot like what you are asking for.

You can find more information on how the unpivot operator works here.

jpw
  • 44,361
  • 6
  • 66
  • 86