如何在SQL Server中进行行到列 [英] How to make row to column in SQL server
本文介绍了如何在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屋!
查看全文