asked on

sql query that displays duplicates in an alias

I have a database that records when computers performs a count of specific activities. The same computer might report several times in a single day. To simplify, we'll say the data is like this:

computer name, date, count
comp1, mon, 3
comp1, mon, 6
comp2, mon, 4
comp3, mon, 1
comp1, tue, 6
comp3, tue, 2
comp2, wed, 12

What I want is for the data to be displayed in a week grid format, like this:

computer name, mon, tue, wed, thu, fri
comp1, 9, 6, 0, 0, 0
comp2, 4, 0, 12, 0, 0
comp3, 1, 2, 0, 0, 0

I'm not quite sure how to approach this, if it's even possible. Any help is appreciated!

Sharath S

try this.

SELECT *
  FROM test
 PIVOT (SUM([Count]) FOR [Date] IN ([mon],[tue],[wed],[thu],[fri],[sat],[sun])) AS p

Open in new window

http://sqlfiddle.com/#!3/bbb3d/1

bigbigpig

ASKER

That's pretty awesome thanks. In my attempt to simplify I left out something which now seems important. The table has an auto-increment primary key for record number. So it's not adding the values as it should, probably because the record_number value is different.

Sharath S

Can you post your table definition, sample data and expected result?

bigbigpig

ASKER

Same as before, just with the record_number in front.

record_number,computer name, date, count
1,comp1, mon, 3
2,comp1, mon, 6
3,comp2, mon, 4
4,comp3, mon, 1
5,comp1, tue, 6
6,comp3, tue, 2
7,comp2, wed, 12

Expected results would be the same as your sqlfiddle query you linked to above.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bigbigpig

ASKER

Perfect - thanks!!