如何从Sql中选择行 [英] how to select rows from Sql

问题描述

i写这个Select语句从表中选择数据以表格的形式查看这个数据

Hi ,

i write this Select statement to select data from table to view this data as a table

select Number,convert(varchar,datein, 111) as [Date],Operator as [Operator],
	(case id when '6' then substring (SMS,2,len(SMS)) else N'' end) [6],
	(case id when '7' then substring (SMS,2,len(SMS)) else N'' end) [7],
	(case id when '8' then substring (SMS,2,len(SMS)) else N'' end) [8],
	(case id when '9' then substring (SMS,2,len(SMS)) else N'' end) [9]
	from [Table-Name] where  Number= 95239 and id in ('6','7','8','9') 
  and datein between @StartDate AND @EndDate
  order by Number,datein

但它给了我这：

but its give me this :

Number    Date	        Operator	6	7	8	9
2345	5/8/2013	X               ABC 			
2345	5/8/2013	X                       CSA		
2345	5/8/2013	X                             SSSS 	
2345	5/9/2013	X                                      SAQ 

我想要展示一行中的所有这些行都是这样的：

I want to show all this rows in one row to be like this :

Number    Date	        Operator	6	7	8	9
2345	5/8/2013	X               ABC    CSA     SSSS 	SAQ 

提前谢谢：）

Thanks in advance :)

推荐答案

你听说过 pivotots [ ^ ]？



在你的情况下，我会建议你使用MAX（）作为聚合函数，例如：

Did you hear about pivots[^]?

In your case i would suggest you to use MAX() as aggregate function, for example:

SELECT Number,convert(varchar,datein, 111) as [Date],Operator as [Operator], [6],[7],[8],[9]
FROM (
     SELECT * 
     FROM [Table-Name]
     WHERE Number= 95239 and id in ('6','7','8','9') AND datein between @StartDate AND @EndDate) AS DT
PIVOT(MAX([SMS]) FOR [id] IN ( [6],[7],[8],[9])) AS PT

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