CODEWITHSUNDEEP
sqlsql-server-2005t-sqlreporting-servicespivot
user112596
user112596

Reputation: 43

SQL Query to get Matrix reporting

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.

Upvotes: 1

Views: 2480

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332581

  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

Upvotes: 1

Rob Farley
Rob Farley

Reputation: 15849

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

Upvotes: 1

Related Questions