views:

509

answers:

2

My "FeedbackSummary" table structure is

GivenBy varchar(50)
GivenTo varchar(50)
Points  decimal(15, 2)

Sample Data

Alice   Janet 4.50
Alice   Bruce 3.50
Bruce   Alice 2.87
Bruce   Janet 4.75
Janet   Alice 5.45
Janet   Bruce 3.78

What I am trying to achieve

GivenBy_GivenTo Alice Bruce Janet
Alice     NULL 3.50 4.50 
Bruce     2.87 NULL 4.75
Janet     5.45 3.78 NULL

Platform: SQL Server 2005 & 2008

How this can be done using Pivot or any other techniques. Can this be achieved using SQL Reporting Services easily?

Thanks in advance.

+1  A: 

Best is to use SSRS. Put a matrix there, with one column on the rows and one on the columns. You can pivot in a query (the PIVOT option), but that doesn't allow for flexibility in reporting.

Rob

Rob Farley
Yes. I tested this data with SSRS with matrix. The report was perfect and even it allowed some customization. Thanks
+1  A: 
  SELECT t.givenby,
         SUM(CASE WHEN t.givento = 'Alice' THEN t.points ELSE NULL END) 'Alice',
         SUM(CASE WHEN t.givento = 'Bruce' THEN t.points ELSE NULL END) 'Bruce',
         SUM(CASE WHEN t.givento = 'Janet' THEN t.points ELSE NULL END) 'Janet'
    FROM TABLE t
GROUP BY t.givenby
OMG Ponies
Close, but you'd need to aggregate this, putting SUM() around each CASE statement, and grouping by GivenBy.But this doesn't suit SSRS at all, because SSRS doesn't like dynamically appearing columns.
Rob Farley
Matrix does not seems to be correct with this query
Corrected missing aggregate calls. Create a stored proc for SSRS if need be - that's how my SSRS are.
OMG Ponies
Ok, and then you use a table to display it. But if you need a new column you need to change both your stored procedure (unless you use dynamic SQL) and your report (regardless of whether you use dynamic SQL or not).
Rob Farley
@Rob: Yes, but it wouldn't change even if you use PIVOT. That's the reality of turning columnar data into rows.
OMG Ponies
Correct. I said something like "You could use PIVOT, but best is to do it in SSRS for flexibility"
Rob Farley
Thanks rexem. The query achieved the purpose. But SSRS offered the flexibility.