sql 列转行

2015-05-19 04:24:28 131 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

帆软 finereport FCRA 考试题库+答案，共收录561题，大部分有答案帆软 finereport FCRA 考试题库+答案，共收录561题，大部分有答案入门基础 11.多选题以下方式中属于FineReport中预览模式的有( ) 分页预览、填报预览、新填报预览、数据分析、移动端预览 16.判断题 FineReport报表工具必须购买激活码后才能使用。错误，可以免费获取激活码。 29.单选题 FineReport是一款利用( )语言开发的报表工具。java 41.多选题访问帮助文档的方式有哪些？设计器中社区菜单下-帮助文档；直接访问http://help.finere 阅读详情

求列转行SQL，需求如图，表结构见一楼回复SQL，非常感谢

...全文

RT-Thread与ROS2通信：micro-ROS在STM32上的移植实践本文详细介绍了在STM32平台上使用RT-Thread实时操作系统移植micro-ROS实现与ROS2通信的实践方案。通过对比rosserial方案，重点阐述了micro-ROS基于XRCE-DDS协议栈在数据可靠性（丢包率低于1%）、双模式通信（UDP/串口）以及C/C++混合编程方面的优势，为嵌入式设备与ROS2的高效通信提供了完整解决方案。阅读详情

w290601645

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

还在加载中灬 2015-05-19

打赏
举报

--动态
DECLARE @SQL VARCHAR(8000)--SQL2005+可以用VARCHAR(MAX)代替
SET @SQL='SELECT [PropertyTypeName]'
SELECT @SQL=@SQL+',MAX(CASE WHEN [ConditionId]='''+CAST(ID AS VARCHAR)+''' THEN ISNULL([ConditionValueName],[ConditionValue]) END)['+[ConditionName]+']'
FROM [sys_propertyTypeCondition] GROUP BY ID,[ConditionName]
SET @SQL=@SQL+'FROM [sys_propertyTypeValue] GROUP BY [PropertyTypeName]'
PRINT @SQL
EXEC(@SQL)
--静态
SELECT [PropertyTypeName]
	,MAX(CASE WHEN [ConditionId]='22' THEN ISNULL([ConditionValueName],[ConditionValue]) END)[建筑类别]
	,MAX(CASE WHEN [ConditionId]='27' THEN ISNULL([ConditionValueName],[ConditionValue]) END)[计价单位]
	,MAX(CASE WHEN [ConditionId]='28' THEN ISNULL([ConditionValueName],[ConditionValue]) END)[计价比例]
	,MAX(CASE WHEN [ConditionId]='29' THEN ISNULL([ConditionValueName],[ConditionValue]) END)[是否修正]
	,MAX(CASE WHEN [ConditionId]='30' THEN ISNULL([ConditionValueName],[ConditionValue]) END)[基准楼层]
	,MAX(CASE WHEN [ConditionId]='31' THEN ISNULL([ConditionValueName],[ConditionValue]) END)[基准面积]
	,MAX(CASE WHEN [ConditionId]='32' THEN ISNULL([ConditionValueName],[ConditionValue]) END)[基准朝向]
	,MAX(CASE WHEN [ConditionId]='33' THEN ISNULL([ConditionValueName],[ConditionValue]) END)[基准景观]
	,MAX(CASE WHEN [ConditionId]='34' THEN ISNULL([ConditionValueName],[ConditionValue]) END)[基准年份]
FROM [sys_propertyTypeValue]
GROUP BY [PropertyTypeName]

-小蕾- 2015-05-19

打赏
举报


IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
go

CREATE TABLE tb(姓名 VARCHAR(10),语文 INT,数学 INT,物理 INT)

INSERT INTO tb VALUES('张三',74,83,93)

INSERT INTO tb VALUES('李四',74,84,94)

go

SELECT * FROM tb

---SQL列转行
SELECT  姓名 ,
        课程 ,
        分数
FROM    tb UNPIVOT ( 分数 FOR 课程 IN ( [语文], [数学], [物理] ) ) t

w290601645 2015-05-19

打赏
举报


/***********************测试数据**********************/

INSERT [dbo].[sys_propertyTypeValue] ([Id], [AreaID], [PropertyType], [PropertyTypeName], [PropertyTypeID], [ConditionId], [ConditionName], [ConditionValue], [ConditionValueName], [ValueType], [IsInput], [Sort], [Creator], [CreatorName], [CreateDate]) VALUES (100, 73, 0, N'低层-平房', 93, 22, N'建筑类别', N'Lowerlayer', N'低层', N'', 0, 1, 7, N'admin', CAST(0x0000A49D00FF6E66 AS DateTime))
INSERT [dbo].[sys_propertyTypeValue] ([Id], [AreaID], [PropertyType], [PropertyTypeName], [PropertyTypeID], [ConditionId], [ConditionName], [ConditionValue], [ConditionValueName], [ValueType], [IsInput], [Sort], [Creator], [CreatorName], [CreateDate]) VALUES (101, 73, 0, N'低层-平房', 93, 27, N'计价单位', N'Square', N'平米', N'', 0, 1, 7, N'admin', CAST(0x0000A49D00FF6E6B AS DateTime))
INSERT [dbo].[sys_propertyTypeValue] ([Id], [AreaID], [PropertyType], [PropertyTypeName], [PropertyTypeID], [ConditionId], [ConditionName], [ConditionValue], [ConditionValueName], [ValueType], [IsInput], [Sort], [Creator], [CreatorName], [CreateDate]) VALUES (102, 73, 0, N'低层-平房', 93, 28, N'计价比例', N'100.00', NULL, N'', 0, 1, 7, N'admin', CAST(0x0000A49D00FF6E70 AS DateTime))
INSERT [dbo].[sys_propertyTypeValue] ([Id], [AreaID], [PropertyType], [PropertyTypeName], [PropertyTypeID], [ConditionId], [ConditionName], [ConditionValue], [ConditionValueName], [ValueType], [IsInput], [Sort], [Creator], [CreatorName], [CreateDate]) VALUES (103, 73, 0, N'低层-平房', 93, 29, N'是否修正', N'Yes', N'是', N'', 0, 1, 7, N'admin', CAST(0x0000A49D00FF6E75 AS DateTime))
INSERT [dbo].[sys_propertyTypeValue] ([Id], [AreaID], [PropertyType], [PropertyTypeName], [PropertyTypeID], [ConditionId], [ConditionName], [ConditionValue], [ConditionValueName], [ValueType], [IsInput], [Sort], [Creator], [CreatorName], [CreateDate]) VALUES (104, 73, 0, N'低层-平房', 93, 30, N'基准楼层', N'5', NULL, N'', 0, 1, 7, N'admin', CAST(0x0000A49D00FF6E79 AS DateTime))
INSERT [dbo].[sys_propertyTypeValue] ([Id], [AreaID], [PropertyType], [PropertyTypeName], [PropertyTypeID], [ConditionId], [ConditionName], [ConditionValue], [ConditionValueName], [ValueType], [IsInput], [Sort], [Creator], [CreatorName], [CreateDate]) VALUES (105, 73, 0, N'低层-平房', 93, 31, N'基准面积', N'100.00', NULL, N'', 0, 1, 7, N'admin', CAST(0x0000A49D00FF6E83 AS DateTime))
INSERT [dbo].[sys_propertyTypeValue] ([Id], [AreaID], [PropertyType], [PropertyTypeName], [PropertyTypeID], [ConditionId], [ConditionName], [ConditionValue], [ConditionValueName], [ValueType], [IsInput], [Sort], [Creator], [CreatorName], [CreateDate]) VALUES (106, 73, 0, N'低层-平房', 93, 32, N'基准朝向', N'East', N'东', N'', 0, 1, 7, N'admin', CAST(0x0000A49D00FF6E87 AS DateTime))
INSERT [dbo].[sys_propertyTypeValue] ([Id], [AreaID], [PropertyType], [PropertyTypeName], [PropertyTypeID], [ConditionId], [ConditionName], [ConditionValue], [ConditionValueName], [ValueType], [IsInput], [Sort], [Creator], [CreatorName], [CreateDate]) VALUES (107, 73, 0, N'低层-平房', 93, 33, N'基准景观', N'暂无', NULL, N'', 0, 1, 7, N'admin', CAST(0x0000A49D00FF6E8C AS DateTime))
INSERT [dbo].[sys_propertyTypeValue] ([Id], [AreaID], [PropertyType], [PropertyTypeName], [PropertyTypeID], [ConditionId], [ConditionName], [ConditionValue], [ConditionValueName], [ValueType], [IsInput], [Sort], [Creator], [CreatorName], [CreateDate]) VALUES (108, 73, 0, N'低层-平房', 93, 34, N'基准年份', N'2003', NULL, N'', 0, 1, 7, N'admin', CAST(0x0000A49D00FF6E95 AS DateTime))
SET IDENTITY_INSERT [dbo].[sys_propertyTypeValue] OFF
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键ID(自增长)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeCondition', @level2type=N'COLUMN',@level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'区域ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeCondition', @level2type=N'COLUMN',@level2name=N'AreaID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'三级物业类型ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeCondition', @level2type=N'COLUMN',@level2name=N'PropertyTypeID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'三级物业类型名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeCondition', @level2type=N'COLUMN',@level2name=N'PropertyTypeName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'基准条件名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeCondition', @level2type=N'COLUMN',@level2name=N'ConditionName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'如果为输入项，需要选择输入值的类型（int|decimal|string等）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeCondition', @level2type=N'COLUMN',@level2name=N'ValueType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否为输入项' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeCondition', @level2type=N'COLUMN',@level2name=N'IsInput'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'选择项(数据字典对应项)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeCondition', @level2type=N'COLUMN',@level2name=N'SelectItem'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeCondition', @level2type=N'COLUMN',@level2name=N'Creator'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeCondition', @level2type=N'COLUMN',@level2name=N'CreatorName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeCondition', @level2type=N'COLUMN',@level2name=N'CreateDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'物业类型基准条件设置' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeCondition'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键ID(自增长)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeValue', @level2type=N'COLUMN',@level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'区域ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeValue', @level2type=N'COLUMN',@level2name=N'AreaID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'四级物业类型ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeValue', @level2type=N'COLUMN',@level2name=N'PropertyType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'四级物业类型名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeValue', @level2type=N'COLUMN',@level2name=N'PropertyTypeName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'三级物业类型ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeValue', @level2type=N'COLUMN',@level2name=N'PropertyTypeID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'条件ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeValue', @level2type=N'COLUMN',@level2name=N'ConditionId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'条件值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeValue', @level2type=N'COLUMN',@level2name=N'ConditionValue'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'条件值类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeValue', @level2type=N'COLUMN',@level2name=N'ValueType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否录入项' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeValue', @level2type=N'COLUMN',@level2name=N'IsInput'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'序号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeValue', @level2type=N'COLUMN',@level2name=N'Sort'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeValue', @level2type=N'COLUMN',@level2name=N'Creator'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeValue', @level2type=N'COLUMN',@level2name=N'CreatorName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeValue', @level2type=N'COLUMN',@level2name=N'CreateDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'物业类型基准条件设置' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_propertyTypeValue'
GO

w290601645 2015-05-19

打赏
举报


/*
*******************测试数据*******************
*/
INSERT [dbo].[sys_propertyTypeCondition] ([Id], [AreaID], [PropertyTypeID], [PropertyTypeName], [ConditionName], [ValueType], [IsInput], [SelectItem], [Creator], [CreatorName], [CreateDate]) VALUES (22, 73, 93, NULL, N'建筑类别', N'', 1, N'BuildingCategory', 7, N'admin', CAST(0x0000A49C0108E8AD AS DateTime))
INSERT [dbo].[sys_propertyTypeCondition] ([Id], [AreaID], [PropertyTypeID], [PropertyTypeName], [ConditionName], [ValueType], [IsInput], [SelectItem], [Creator], [CreatorName], [CreateDate]) VALUES (27, 73, 93, N'test', N'计价单位', N'', 1, N'ValuationUnit', 7, N'admin', CAST(0x0000A49C0112459D AS DateTime))
INSERT [dbo].[sys_propertyTypeCondition] ([Id], [AreaID], [PropertyTypeID], [PropertyTypeName], [ConditionName], [ValueType], [IsInput], [SelectItem], [Creator], [CreatorName], [CreateDate]) VALUES (28, 73, 93, N'test', N'计价比例', N'Decimal', 0, N'', 7, N'admin', CAST(0x0000A49C011275BF AS DateTime))
INSERT [dbo].[sys_propertyTypeCondition] ([Id], [AreaID], [PropertyTypeID], [PropertyTypeName], [ConditionName], [ValueType], [IsInput], [SelectItem], [Creator], [CreatorName], [CreateDate]) VALUES (29, 73, 93, N'test', N'是否修正', N'', 1, N'Revise', 7, N'admin', CAST(0x0000A49C01128C22 AS DateTime))
INSERT [dbo].[sys_propertyTypeCondition] ([Id], [AreaID], [PropertyTypeID], [PropertyTypeName], [ConditionName], [ValueType], [IsInput], [SelectItem], [Creator], [CreatorName], [CreateDate]) VALUES (30, 73, 93, N'test', N'基准楼层', N'Int', 0, N'', 7, N'admin', CAST(0x0000A49C0112A04F AS DateTime))
INSERT [dbo].[sys_propertyTypeCondition] ([Id], [AreaID], [PropertyTypeID], [PropertyTypeName], [ConditionName], [ValueType], [IsInput], [SelectItem], [Creator], [CreatorName], [CreateDate]) VALUES (31, 73, 93, N'test', N'基准面积', N'Decimal', 0, N'', 7, N'admin', CAST(0x0000A49C0112ADA5 AS DateTime))
INSERT [dbo].[sys_propertyTypeCondition] ([Id], [AreaID], [PropertyTypeID], [PropertyTypeName], [ConditionName], [ValueType], [IsInput], [SelectItem], [Creator], [CreatorName], [CreateDate]) VALUES (32, 73, 93, N'test', N'基准朝向', N'', 1, N'Orientation', 7, N'admin', CAST(0x0000A49C0112C077 AS DateTime))
INSERT [dbo].[sys_propertyTypeCondition] ([Id], [AreaID], [PropertyTypeID], [PropertyTypeName], [ConditionName], [ValueType], [IsInput], [SelectItem], [Creator], [CreatorName], [CreateDate]) VALUES (33, 73, 93, N'test', N'基准景观', N'String', 0, N'', 7, N'admin', CAST(0x0000A49C0112CF1D AS DateTime))
INSERT [dbo].[sys_propertyTypeCondition] ([Id], [AreaID], [PropertyTypeID], [PropertyTypeName], [ConditionName], [ValueType], [IsInput], [SelectItem], [Creator], [CreatorName], [CreateDate]) VALUES (34, 73, 93, N'test', N'基准年份', N'Int', 0, N'', 7, N'admin', CAST(0x0000A49C0112DCF0 AS DateTime))
SET IDENTITY_INSERT [dbo].[sys_propertyTypeCondition] OFF
SET IDENTITY_INSERT [dbo].[sys_propertyTypeValue] ON

w290601645 2015-05-19

打赏
举报



/****** Object:  Table [dbo].[sys_propertyTypeCondition]    Script Date: 2015-5-19 16:09:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sys_propertyTypeCondition](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[AreaID] [bigint] NULL,
	[PropertyTypeID] [bigint] NULL,
	[PropertyTypeName] [nvarchar](200) NULL,
	[ConditionName] [nvarchar](200) NULL,
	[ValueType] [nvarchar](50) NULL,
	[IsInput] [int] NULL,
	[SelectItem] [nvarchar](50) NULL,
	[Creator] [bigint] NULL,
	[CreatorName] [nvarchar](100) NULL,
	[CreateDate] [datetime] NULL,
 CONSTRAINT [PK_SYS_PROPERTYCONDITION] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[sys_propertyTypeValue]    Script Date: 2015-5-19 16:09:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sys_propertyTypeValue](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[AreaID] [bigint] NULL,
	[PropertyType] [bigint] NULL,
	[PropertyTypeName] [nvarchar](200) NULL,
	[PropertyTypeID] [bigint] NULL,
	[ConditionId] [bigint] NULL,
	[ConditionName] [nvarchar](50) NULL,
	[ConditionValue] [nvarchar](200) NULL,
	[ConditionValueName] [nvarchar](50) NULL,
	[ValueType] [nvarchar](50) NULL,
	[IsInput] [bit] NULL,
	[Sort] [int] NULL,
	[Creator] [bigint] NULL,
	[CreatorName] [nvarchar](100) NULL,
	[CreateDate] [datetime] NULL,
 CONSTRAINT [PK_SYS_PROPERTYTYPECONDITION] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

sql 列转行

相关推荐