SELECT rows for each 2 DISTINCT columns in a Table in SQL Server

Question

I have a single table that has thousands of rows of log statistics (number of views per page, per website) for multiple websites. For instance, in this table, multiple sites can have a separate stat for the same page name:

+----+---------+-------+------------+
| id | page    | views |  sitename  |
+----+---------+-------+------------+
|  1 |   page1 |    7  |   name1    |
+----+---------+-------+------------+
|  2 |   page1 |    5  |   name2    |
+----+---------+-------+------------+
|  3 |   page2 |    3  |   name2    |
+----+---------+-------+------------+
|  4 |   page1 |    7  |   name3    |
+----+---------+-------+------------+
|  5 |   page2 |    5  |   name3    |
+----+---------+-------+------------+
|  6 |   page3 |    3  |   name3    |
+----+---------+-------+------------+

I am trying to formulate a query that will allow me to list each DISTINCT page name, for each DISTINCT sitename -- and display the number of views for that page per site:

+-------+----------+-------+---------+
| page  | name1    | name2 |    name3|
+-------+----------+-------+---------+
| page1 | 7        |    5  |       7 |
+-------+----------+-------+---------+
| page2 | NULL     |    3  |       5 |
+-------+----------+-------+---------+
| page3 | NULL     |  NULL |       3 |
+-------+----------+-------+---------+

If a site does not have a stat for that particular page, the view value should be NULL. This leads me to think I'll need to do a JOIN/UNION some place, but I can't wrap my mind around it! How can I formulate this query when I need multiple distinct values? Thanks!

UPDATE (ANSWER WITH DYNAMIC SQL + PIVOT): PIVOT was the correct method, which both potential answers included. Gave the official credit to 2nd responder since the SUM of the values in the actual SELECT statement was the key to getting the dynamic SQL query to work properly.

http://sqlfiddle.com/#!6/7b4cb/37/0

Also, used some TSQL from here:

SQL Server Pivot with Dynamic Fields

It seems like the data already exist and this is just a presentation question. If I was doing this for a Web page, I would sort your table and make the presentation in server side Web code. EDIT: A pivot table as suggested by @RohitSingh looks like a very good suggestion. — mikeY, Jun 18 '13 at 18:08
Assuming the list of site names varies, a dynamic pivot may be in order: http://stackoverflow.com/questions/2209700/how-to-use-pivot-in-sql-server-2005-stored-procedure-joining-two-views — John Dewey, Jun 18 '13 at 18:14
There are 100+ sites (and growing), so dynamic is definitely required here. Let me take a look at pivot.... — tresstylez, Jun 18 '13 at 18:21

score 2 · Accepted Answer · edited May 23 '17 at 11:59

2

If the site list is static you can use a pivot like so

SELECT 
  page,
  sum(name1) name1,
  sum(name2) name2,
  sum(name3) name3
FROM

(SELECT id, page, views, sitename 
FROM Table1) p 
PIVOT
(
  SUM(VIEWS)
  FOR SiteName IN (name1, name2, name3) 
 ) as pvt
GROUP BY 
  page

SQL Fiddle

If it needs to be dynamic you can use this technique that uses dynamic sql.

Bluefeet gives an example here

edited May 23 '17 at 11:59

Community

1
1

answered Jun 18 '13 at 18:10

Conrad Frix

51,984
12
96
155

ALSO, why does SUM on the column name work here, even though its a VARCHAR?? In SQL Fiddle it does not complain, but in SQL Server, its gives me 'Operand data type varchar is invalid for sum operator'. Any ideas how to fix this? – tresstylez Jun 19 '13 at 00:20

score 1 · Answer 2 · answered Jun 18 '13 at 18:08

You can use Pivot functionality for the above mentioned scenario. Please find the implementation below:

http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

SELECT rows for each 2 DISTINCT columns in a Table in SQL Server

2 Answers2