数据库表机构内容如下:
id(主) nid date_time n1 n2 n3 n4 n5
1 87 2010-03-30 00:00:00 62 16 20 25.81% NULL
2 87 2010-03-31 00:00:00 54 17 18 31.48% NULL
3 88 2010-03-30 00:00:00 65 19 22 29.23% NULL
4 88 2010-03-31 00:00:00 58 18 21 31.03% NULL如何转换成下面的格式:
日期 87 88
2010-03-30 62 16 20 25.81% null 65 19 22 29.23% null
2010-03-31 54 17 18 31.48% null 58 18 21 31.03% null
用sql语句如何实现?
id(主) nid date_time n1 n2 n3 n4 n5
1 87 2010-03-30 00:00:00 62 16 20 25.81% NULL
2 87 2010-03-31 00:00:00 54 17 18 31.48% NULL
3 88 2010-03-30 00:00:00 65 19 22 29.23% NULL
4 88 2010-03-31 00:00:00 58 18 21 31.03% NULL如何转换成下面的格式:
日期 87 88
2010-03-30 62 16 20 25.81% null 65 19 22 29.23% null
2010-03-31 54 17 18 31.48% null 58 18 21 31.03% null
用sql语句如何实现?
我想用HQL语句直接查处封装,然后界面上直接迭代对象。
-- Author : htl258(Tony)
-- Date : 2010-04-24 10:55:00
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[nid] INT,[date_time] DATETIME,[n1] INT,[n2] INT,[n3] INT,[n4] NVARCHAR(10),[n5] NVARCHAR(10))
INSERT [tb]
SELECT 1,87,N'2010-03-30',62,16,20,N'25.81%',NULL UNION ALL
SELECT 2,87,N'2010-03-31',54,17,18,N'31.48%',NULL UNION ALL
SELECT 3,88,N'2010-03-30',65,19,22,N'29.23%',NULL UNION ALL
SELECT 4,88,N'2010-03-31',58,18,21,N'31.03%',NULL
GO
--SELECT * FROM [tb]-->SQL查询如下:
SELECT *
FROM (
SELECT [DATE_TIME],NID,
ISNULL(LTRIM(N1),'NULL')+' '+ISNULL(LTRIM(N2),'NULL')+' '+ISNULL(LTRIM(N3),'NULL')
+' '+ISNULL(LTRIM(N4),'NULL')+' '+ISNULL(LTRIM(N5),'NULL') AS NALL
FROM TB
) T
PIVOT(MAX(NALL) FOR NID IN([87],[88])) B
/*
DATE_TIME 87 88
2010-03-30 00:00:00.000 62 16 20 25.81% NULL 65 19 22 29.23% NULL
2010-03-31 00:00:00.000 54 17 18 31.48% NULL 58 18 21 31.03% NULL
*/