在SQL Server的表中为每个2 DISTINCT列选择行

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:

我正在尝试制定一个查询,允许我为每个DISTINCT网站名称列出每个DISTINCT页面名称 - 并显示每个网站的该页面的视图数量:

+-------+----------+-------+---------+
| 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!

如果站点没有该特定页面的统计信息,则视图值应为NULL。这让我觉得我需要在某个地方做一个JOIN / UNION,但我无法绕过它!当我需要多个不同的值时,如何制定此查询?谢谢!

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.

更新(动态SQL + PIVOT的答案):PIVOT是正确的方法,包括两个潜在的答案。由于实际SELECT语句中的值的SUM是使动态SQL查询正常工作的关键,因此给第二响应者提供了官方信用。

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

Also, used some TSQL from here:

另外,从这里使用了一些TSQL:

SQL Server Pivot with Dynamic Fields

带有动态字段的SQL Server Pivot

2 个解决方案

#1

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.

如果它需要是动态的,你可以使用这种使用动态sql的技术。

Bluefeet gives an example here

Bluefeet在这里给出了一个例子

#2

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

您可以将Pivot功能用于上述方案。请在下面找到实施方案:

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/

#1