Hi all. So I have a table that is essentially row-based that I would like to pivot to column. The schema is as follows:RefTable-Id-DateOfEntry-ValuePrimaryTable-Id-NameI have been able to successfully pivot the 'RefTable'. For me this means transposing the Id as the column name and summing the Value field. I used the following pivot statement which also includes a parameter (@Ids) with the list of all Ids I wish to transpose.SET @query ='SELECT * FROM( SELECT DateOfEntry, Id, Value FROM RefTable)tPIVOT (SUM(Value) FOR IdIN (' + @Ids + ')) AS pvt'EXECUTE (@query)Now the problem I'm having has to do with the PrimaryTable I provided above. You see I would like to use the results of this as my data source to bind to a control (such as Gridview). The problem however is that I need both the 'Id' field and the 'Name' field for my final dataset. So I can't seem to figure out how to return the Name along with the Ids. I've tried to expand my query by pivoting on multiple columns but haven't been successful, nor do I know if this is the right approach.So I guess the final result set could be something like this:-DateOfEntry-Id (would have the Value)-Name (would have the Value as well)If you need more information, please feel free to ask. Thanks
Pivot columns
Posted - 2012-10-23 : 13:27:36
