0

I am struggling to see how to do what i expect is a simple pivot. Although this is different to all the examples of pivot that I have seen.

Dummy structure as follows:

I have a attributes stored against a specific Source ID as follows:

Attribute Table

SrcId    Size    Color  
123     Small    Green
456     Medium   Yellow
789     Large    Red

I now have data for these Source Id's that looks like this

Data Table

Id    SrcId  Amount
1     123    1000
2     123    2000
3     123    3000
4     123    4000
5     456    1000
6     456    5000
7     456    7000
8     789    2000
9     789    3000

I know that data doesnt make much sense and what I am trying to do doesnt seem very useful but this is what we need to do. We need to do list all the source attributes and transpose the data alongside the attributes in columns, as follows:

Results View

SrcId    Size    Color    Amount1   Amount2   Amount3
123      Small    Green    1000      2000      3000
456      Medium   Yellow   1000      5000      7000
789      Large    Red      2000      3000      NULL

Note how for SrcId 123 the additional Amount of 4000 was left out because we are only retrieving 3 columns worth of data for each source.

Any ideas please advise. Thanks

jarlh
  • 42,561
  • 8
  • 45
  • 63
Gotts
  • 2,274
  • 3
  • 23
  • 32
  • I deleted the previous comment as it left out the most crucial part. You need to rank each amount value within your SrcID group by some order then you can use an average aggregate based on your id field and value rank field...Join your two tables and look for a way to rank the values see ROW_NUMBER() OVER PARTITION, for example. – Ross Bush Apr 26 '17 at 13:23
  • i didnt see the previous comment. Ok will try what you suggested. Thanks – Gotts Apr 26 '17 at 13:31
  • Select SrcId,Size,Color,Amount1=[1],Amount2=[2],Amount3=[3] From ( Select B.* ,RN = Row_Number() over (Partition By A.SrcId Order by A.Id) ,A.Amount From Data A Join Attribute B on (A.SrcId=B.SrcId) ) A Pivot (max(Amount) For [RN] in ([1],[2],[3]) ) p – John Cappelletti Apr 26 '17 at 13:38
  • Thanks. I pretty much went with the answer found here and as per Ross's comments http://stackoverflow.com/questions/20999264/transpose-rows-and-columns-with-no-aggregate – Gotts Apr 26 '17 at 17:03

0 Answers0