Complete newbie! How to organize data from a complicated SharePoint list

[JoelDucharme]

Hi I have a very wide sharepoint list that I need to organize. It looks a bit like this:

ID	l1_total	l1_project	l1_activity	l1_description	l2_total	l2_project	l2_activity	l2_description	Receipt #	Attachements	Supplier
1	20$	Project 1	Activity 1	Description1	10$	Project 2	Activity 2	Description2	1234	Attachement 1 Attachement2 Attachement 3	Supplier1
2	100$	Project 3	Activity 1	Description3	123$	Project 1	Activity 1	Description 4	1235	null	Supplier2
3	25$	Project 1	Activity 2	Description5	null	null	null	null	null	null	Supplier3

How can I manipulate the data to build a report that might look like this

ID	Total	Project	Activity	Description	Receipt#	Attachements	Supplier
1	20$	Project 1	Activity 1	Description1	1234	Attachements 1,2 and 3	Supplier1
1	10$	Project 2	Activity 2	Description2	1234	Attachements 1,2 and 3	Supplier1
2	100$	Project 3	Activity 1	Description 3	1235	null	Supplier2
2	123$	Project 1	Activity 1	Description 4	1235	null	Supplier2
3	25$	Project 1	Activity 2	Description5	null	null	Supplier3

My table is actually alot bigger with 10 project, activity, total, description (and more) collumns. I tried to unpivot, but when I unpivot all those collumns, i only get all the info for one ID. All the others dissapear. And, the new lines created by the unpivots don't seem to line up. When I unpivot the second set of collumns, I get double the lines, and then quadruple, etc.... I'm surely doing something wrong. 

[JoelDucharme]

Thanks for your help. I created all my queries, but how do I append them all together? When I use the "append queries as new", I basically just get the original list. 

[Greg_Deckler · Accepted solution]

@JoelDucharme Use Append Queries as New, select Three or more tables and then add all your tables.

[JoelDucharme]

Thanks! That's what I had done and it didn't seem right. I looked at it further and it's what I need. The steps that were missing for me though is I had to combine the differents line collumns. For example, I combined l1_project, l2_project, l3_project, etc... to make one "Project" collumn. After that though, I had a whole bunch of entries that were empty, except for the ID collumn. To remedy that, I made a new combined collumn, deleted the empty ones and then deleted this new collumn to keep it clean. 

All of this worked. Thanks for your help.

[Greg_Deckler]

@JoelDucharme Create 10 queries that each pull only the fields required for each Project group of columns plus the Receipt, #Attachments and Supplier columns. Set them all to not load and then create a query that appends them all together.