select id,min(col) as col from (select id,col1 as col from tb union all select id,col2 from tb .... )as t group by id
如果行数不固定 就用动态SQL 结合syscolumns表来做。
use Tempdb go --> -->
if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([ID] nvarchar(2),[COL1] int,[COL2] int,[COL3] int,[COL4] int) Insert #T select N'01',1,2,4,1 union all select N'02',3,5,8,2 union all select N'03',6,7,3,3 union all select N'04',4,5,6,7 Go Select ID ,min(VALUE) AS VALUE from #T UNPIVOT( VALUE FOR Cols IN ([COL1],[COL2],[COL3],[COL4]) --加上Col1...Col10 ) AS b GROUP BY ID /* 01 1 02 2 03 3 04 4 */
如果是SQL Server 2012可以用IIF 套IIF,如果低于2012,可以参考下面的方法:CREATE TABLE [X] ( [ID] INT, [Date1] DATETIME, [Date2] DATETIME, [Date3] DATETIME )INSERT [X] VALUES (0, '09/29/2011', '09/20/2011', '09/01/2011'), (1, '01/01/2011', '01/05/2011', '03/03/2010') SELECT [ID], MIN([Date]) AS [MinDate] FROM [X] UNPIVOT ( [Date] FOR d IN ([Date1] ,[Date2] ,[Date3]) ) unpvt GROUP BY [ID]
id,min(col) as col
from
(select id,col1 as col from tb
union all
select id,col2 from tb
....
)as t
group by
id
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] nvarchar(2),[COL1] int,[COL2] int,[COL3] int,[COL4] int)
Insert #T
select N'01',1,2,4,1 union all
select N'02',3,5,8,2 union all
select N'03',6,7,3,3 union all
select N'04',4,5,6,7
Go
Select ID ,min(VALUE) AS VALUE
from #T
UNPIVOT(
VALUE FOR Cols IN ([COL1],[COL2],[COL3],[COL4]) --加上Col1...Col10
) AS b
GROUP BY ID
/*
01 1
02 2
03 3
04 4
*/
(
[ID] INT,
[Date1] DATETIME,
[Date2] DATETIME,
[Date3] DATETIME
)INSERT [X]
VALUES (0, '09/29/2011', '09/20/2011', '09/01/2011'),
(1, '01/01/2011', '01/05/2011', '03/03/2010')
SELECT [ID], MIN([Date]) AS [MinDate]
FROM [X]
UNPIVOT (
[Date] FOR d IN
([Date1]
,[Date2]
,[Date3])
) unpvt
GROUP BY [ID]