How to get Matrix format query in T-SQL

Question

Post reply

How to get Matrix format query in T-SQL

gnmohan17

SSC Veteran

Points: 245
More actions
October 21, 2016 at 8:25 pm

#331496

hi,
I want T-SQL query for getting output in Matrix format like below table view.I am using MS-Access as Database.
INPUT TABLE
NAME,SALARY,DATE
SAM-1,$67,21-10-2016
SAM-2,$34,21-10-2016
SAM-1,$39,20-10-2016
SAM-2,$45,20-10-2016
REQUIRED OUTPUT LIKE
NAME,21-10-2016,20-10-2016
SAM-1,$67,$39
SAM-2,$34,$45
THANK YOU

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

DECLARE @yourTable TABLE(Name varchar(100), Salary money, dt date);

INSERT @yourTable VALUES

('SAM-1',$67,'20161021'),

('SAM-2',$34,'20161021'),

('SAM-1',$39,'20161020'),

('SAM-2',$45,'20161020');

SELECT

Name,

[21-10-2016] = SUM(CASE dt WHEN '20161021' THEN Salary END),

[20-10-2016] = SUM(CASE dt WHEN '20161020' THEN Salary END)

FROM @yourTable

GROUP BY Name;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1003888 More actions · Answer 2

To explain the ancient but very effective CROSS TAB method that Alan used, please see the following article, which comes complete with a performance test that demonstrates one of the many reasons why I think it's superior to the PIVOT operator.

[font="Arial Black"]Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/font][/url]

For an easy method to make it more dynamic and parameter flexible, please see the following article.

[font="Arial Black"]Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/font][/url]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)