如何在SQL Server中进行行到列 [英] How to make row to column in SQL server

问题描述

我有一个包含值的表格，如

I have a table it contains value like

create table tableName (
dattime datetime, id int)

insert into tableName (dattime,id) values ('2019-05-07 07:15:000',1)
insert into tableName (dattime,id) values ('2019-05-07 13:50:000',1)
insert into tableName (dattime,id) values ('2019-05-07 07:15:000',2)
insert into tableName (dattime,id) values ('2019-05-07 13:50:000',2)

dattime                 | id
2019-05-07 07:15:00.000	| 1
2019-05-07 13:50:00.000	| 1
2019-05-07 07:15:00.000	| 2
2019-05-07 13:50:00.000	| 2 

我的问题是如何将此值设为

My Question is how to make this value as

id  |   col1              | col2
1   |   2019-05-07 07:15  | 2019-05-07 13:50
2   |   2019-05-07 07:15  | 2019-05-07 13:50

请帮忙！.....



我尝试过：

Please help!.....

What I have tried:

SELECT id, dattime FROM   
(SELECT id, dattime  FROM tableName )col1
PIVOT  (  
max(id) FOR dattime IN (dattime)) AS col2

推荐答案

最简单的方法就是使用GROUP BY：

The simplest way is just to use GROUP BY:

SELECT ID, MIN(Dattime) AS Col1, MAX(Dattime) As Col2
FROM tableName 
GROUP BY ID

您好

我认为您需要获取代码中的数据，如上所述，可能是报表或图表，更好地声明数据表并使用查询来检索一个接一个地通过编程代码在该数据表中动态绑定值。



使用t他的查询仅使用id

Hi
I think u need to get data in code like u mention above may be for report or chart , better declare data-table and use query to retrieve one by one and bind value dynamically in that data-table by programming code.

use this query to take id only

select id  from tablename group by id

从tablename group中选择id，然后循环结果，

e：g id'1和2 u将获得每个结果为2，

所以第一个值绑定在第1列，第2个值绑定在第2列，同样类似于id - 2

第一个值绑定在第1列和第2个值在第2栏中绑定



问候，

Aravind

, then loop with result,
e:g id '1 and 2 u will get 2 result each,
so first value bind in col 1 and 2 value bind in col 2 , same like for id - 2
first value bind in col 1 and 2 value bind in col 2

Regards,
Aravind

这篇关于如何在SQL Server中进行行到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！