-2

I've been struggling with a challenge I've inherited which I think is possible but can't easily see what the solution would be. It's along the lines of the return rows as columns scenario (which I appreciate there are a lot of posts about already but I think this is slightly different). I thought a PIVOT would be useful but I don't need to aggregate any values. The metadata describing each document is defined at folder level so documentID = 1 should be tagged with the values 111, ABC, DEF and GHI.

So I have a table as follows:

Current Dataset:

enter image description here

The aim is to have the data presented as this instead so all the metadata tags for both the folders and document are stored in one row (ultimately I'll be exporting to CSV)

Desired Dataset enter image description here

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Nigel Maddocks
  • 1
  • Use Pivot it is so easy – Lukasz Szozda Nov 24 '15 at 16:14
  • 5
    Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Tab Alleman Nov 24 '15 at 16:17
  • I persevered with PIVOT and after a bit of trial and error it seems to be doing what I want so thanks for making me revisit it again! I used the MAX aggregate function which seems to do the trick. Thanks. – Nigel Maddocks Nov 24 '15 at 17:29

1 Answers1

0

I have the same problem. Not worked out completely yet. I have only done it twice now manually. And planning on making a macro.

I work with vertical lookup and sorting and deleting data.

In your case sort the data based on metatag_value, trasnfer all the BBB, CCC, etc to new columns. then write a VLookup in the Metag_value2, etc columns at the AAA row.

Copy/paste the entire datasheet as values to get rid of the formulas, and then delete all the rows without AAA in them.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tim Van Sprang-Könning
  • 1