如何获得输出如下所示？请帮我。 [英] How do I get output as show follows? Please help me.
问题描述
我的数据库如下：
sr。没有。 URL日期访问
1 gmail.com 4/1/2014 5
2 facebook.com 4/1/2014 1
3 twitter。 com 4/1/2014 3
4 orkut.com 2014年4月1日1
5 gmail.com 4/8/2014 7
6 facebook.com 2014/4/8 9
7 twitter.com 2014/4/8 4
8 orkut.com 4/8/2014 63
9 gmail.com 4/15/2014 1
10 facebook.com 2014/4/15 3
11 twitter.com 4/15/2014 6
12 orkut.com 2014/4/15 1
13 gmail.com 4/22/2014 6
14 facebook.com 4 / 22/2014 23
15 twitter.com 2014/4/22 1
16 orkut.com 2014/4/22 84
17 gmail.com 2014/4/29 12
18 facebook.com 4/29/2014 9
19 twitter.com 4/29/2014 8
20 orkut.com 2014/4/29 8
输出如下：
My database is as follow:
sr. no. URL Date Visits
1 gmail.com 4/1/2014 5
2 facebook.com 4/1/2014 1
3 twitter.com 4/1/2014 3
4 orkut.com 4/1/2014 1
5 gmail.com 4/8/2014 7
6 facebook.com 4/8/2014 9
7 twitter.com 4/8/2014 4
8 orkut.com 4/8/2014 63
9 gmail.com 4/15/2014 1
10 facebook.com 4/15/2014 3
11 twitter.com 4/15/2014 6
12 orkut.com 4/15/2014 1
13 gmail.com 4/22/2014 6
14 facebook.com 4/22/2014 23
15 twitter.com 4/22/2014 1
16 orkut.com 4/22/2014 84
17 gmail.com 4/29/2014 12
18 facebook.com 4/29/2014 9
19 twitter.com 4/29/2014 8
20 orkut.com 4/29/2014 8
And the output look like this:
URLs 4/1/2014 4/8/2014 4/15/2014 4/22/2014 4/29/2014
gmail.com 5 7 1 6 12
facebook.com 1 9 3 23 9
twitter.com 3 4 6 1 8
orkut.com 1 63 1 84 8
这是输出表来自上面的数据库表，其标题是asc中的不同日期，并显示各个日期的明确url明智访问。请帮帮我。
that is the output table come from above database table whose header is distinct date in asc and distinct url wise visits are displayed for respective dates. please help me.
推荐答案
枢轴 [ ^ ]就是你想要的;）
Pivot[^] is that what you want ;)
CREATE TABLE #tmp (SrNo INT IDENTITY(1,1), URL VARCHAR(50), VisDate DATETIME, Visits INT)
SET DATEFORMAT mdy;
INSERT INTO #tmp (URL , VisDate , Visits)
VALUES('gmail.com', '4/1/2014', 5),
('facebook.com', '4/1/2014', 1),
('twitter.com', '4/1/2014', 3),
('orkut.com', '4/1/2014', 1),
('gmail.com', '4/8/2014', 7),
('facebook.com', '4/8/2014', 9),
('twitter.com', '4/8/2014', 4),
('orkut.com', '4/8/2014', 63),
('gmail.com', '4/15/2014', 1),
('facebook.com', '4/15/2014', 3),
('twitter.com', '4/15/2014', 6),
('orkut.com', '4/15/2014', 1),
('gmail.com', '4/22/2014', 6),
('facebook.com', '4/22/2014', 23),
('twitter.com', '4/22/2014', 1),
('orkut.com', '4/22/2014', 84),
('gmail.com', '4/29/2014', 12),
('facebook.com', '4/29/2014', 9),
('twitter.com', '4/29/2014', 8),
('orkut.com', '4/29/2014', 8)
DECLARE @cols VARCHAR(300)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(MAX)
SET @cols = STUFF((SELECT DISTINCT '],[' + CONVERT(VARCHAR(10), VisDate, 121)
FROM #tmp
WHERE VisDate BETWEEN '4/1/2014' AND '4/30/2014'
FOR XML PATH('')), 1, 2, '') + ']'
--SELECT @cols AS Cols
SET @dt = 'SELECT URL, CONVERT(VARCHAR(10),VisDate,121) AS VisDate, Visits FROM #tmp'
SET @pt = 'SELECT URL, ' + @cols + ' ' +
'FROM(' + @dt + ') AS DT ' +
'PIVOT(SUM(Visits) FOR VisDate IN (' + @cols + ')) AS PT'
EXEC(@pt)
DROP TABLE #tmp
结果：
Result:
URL 04/01.. 04/08.. 04/15.. 04/22.. 04/29..
facebook.com 1 9 3 23 9
gmail.com 5 7 1 6 12
orkut.com 1 63 1 84 8
twitter.com 3 4 6 1 8
[/ EDIT]
[/EDIT]
我认为表中出现的 date 可能会不时变化。这就是为什么你必须使用Pivot动态查询。
请在下面找到你的问题解决方案的评论：
I think that the date appears in table may vary time to time. That's why you have to use Pivot with dynamic query.
Please find below the solution of your question with comments:
-- Retrieve the distinct Dates from table and concatinate it with format [Date1],[Date2]..
DECLARE @dateCols VARCHAR(MAX), @query VARCHAR(MAX)
SELECT @dateCols=COALESCE(@dateCols+',','') + '['+[Date]+']' FROM (Select distinct [Date] from [table1]) tmp
-- Define dynamic query along with the dates that we have fecthed above. In this query we have used PIVOT to make the row data as column names..
SET @query='SELECT [URL], '+@dateCols+'
FROM (
SELECT * FROM [table1]
) tbl
PIVOT
(SUM([Visits]) FOR [Date] IN ('+@dateCols+')) pvt'
-- Execute a dynamic query
EXEC @query
I我建议你只对小数据使用这个查询，因为如果表中有很多 date ，那么SQL将所有日期作为列就变得繁琐。你也可以拿到号码。通过使用 date 逐月访问，这对于大量数据集更容易显示。
我希望这个解决方案可以帮到你。
I would like to suggest you to use this query for small data only because if lots of dates available in table then it becomes tedious job for SQL to make all dates as column. You can also fetch the no. of visits month wise by using dates which would be easier to display for large set of data.
I hope this solution will help you.
这篇关于如何获得输出如下所示？请帮我。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
