Sql Server查询创建 [英] Sql Server Query Creation

问题描述

我有这样的表格

i have table like this

Name	Date
-------------------------------
Ravi	13/08/2013
Axay	13/08/2013
Ravi	13/08/2013
Ravi	13/08/2013
Axay	13/08/2013
Axay	14/08/2013
Ravi	14/08/2013
Ravi	14/08/2013
Axay	14/08/2013
Ravi	15/08/2013
Axay	15/08/2013
Axay	15/08/2013

现在我想要这种格式的数据

Now i want data in this format

Date 	       Ravi	Axay
-------------------------------
13/08/2013	3	2
14/08/2013	2	2
15/08/2013	1	2

为此我尝试了很多查询，但我没有成功，所以请给我查询以获得上述结果的这种类型。

推荐答案

检查这个



SQL SERVER - PIVOT和UNPIVOT表示例 [ ^ ]

您没有指定哪个版本的SQL - 您正在使用的服务器，但对于SQL-Server 2005及更高版本，您可以使用 PIVOT [ ^ ]。



您想要计算每个名称的日期数，并将每个唯一名称转换为一列。



所以th你不想PIVOT的e列是 Date 列，这将是你的PIVOT查询中的第一列。唯一名称将是查询中的下一列。



如果要计算名称数量， SUM [ ^ ]将是要使用的聚合函数。



将所有这些放在一起，得到的查询是：

You did not specify which version of SQL-Server you are using, but for SQL-Server 2005 and higher you can use PIVOT[^].

You want to count the number of dates for each name and turn each unique name into a column.

So the the column you do not want to PIVOT is the Date column, this will be the first column in your PIVOT query. The unique names will be the next columns in the query.

As you want to count the number of names, SUM[^] will be the aggregated function to use.

Put this all together and the resulting query is:

  SELECT [Date], [Ravi], [Axay]
    FROM (SELECT [Date], [Name]
            FROM [MyTable]) AS [SourceTable]
   PIVOT (COUNT([Name])
          FOR [Name] IN ([Ravi], [Axay]) ) AS [PivotTable]
ORDER BY [PivotTable].[Date]

请注意，如果您在表格中添加第三个名称并希望它显示，则需要改变查询。

Note that if you add a third name into the table and want it to display you will need to alter the query.

这是一个如何获得预期结果的简单示例。其他人可能知道更优雅的方式来获得相同的结果。请注意，此示例中的 Select 语句必须包含名称的所有可能值。



创建一个名为MYTABLE的空表

This is a simple example of how to get the results you expect. Others may know of a more elegant way to get the same results. Note that the Select statement in this example must include all possible values of Name.

Create an empty table named MYTABLE

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable](
	[Name] [nvarchar](30) NOT NULL,
	[Date] [date] NOT NULL
) ON [PRIMARY]
GO

插入一些测试数据

Insert some test data

insert into mytable (name,date)
SELECT 'Ravi','2013-08-13' union all
SELECT 'Axay','2013-08-13' union all
SELECT 'Ravi','2013-08-13' union all
SELECT 'Ravi','2013-08-13' union all
SELECT 'Axay','2013-08-13' union all
SELECT 'Axay','2013-08-14' union all
SELECT 'Ravi','2013-08-14' union all
SELECT 'Ravi','2013-08-14' union all
SELECT 'Axay','2013-08-14' union all
SELECT 'Ravi','2013-08-15' union all
SELECT 'Axay','2013-08-15' union all
SELECT 'Axay','2013-08-15' 

一个简单的查询

A simple query

select date,
 (select count(*) from mytable as t1 where t1.date=t0.date AND name='Ravi') as 'Ravi',
 (select count(*) from mytable as t2 where t2.date=t0.date AND name='Axay') as 'Axay'
from mytable as t0
group by date
order by date

结果

The results

date       	Ravi	Axay
2013-08-13	3	2
2013-08-14	2	2
2013-08-15	1	2

这篇关于Sql Server查询创建的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！