sql 列转行

w290601645 2015-05-19 04:24:28
求列转行SQL，需求如图，表结构见一楼回复SQL，非常感谢

...全文
118 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
还在加载中灬 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行转列、列转行
本文介绍SQL中行转列与列转行的基本方法及应用场景，通过实例演示如何使用CASE语句进行数据聚合统计，并展示了不同场景下的列转行实现方式。
sql列转行 行转列
本文介绍在SQL中进行列转行和行转列操作的多种方法，包括使用UNION ALL、UNPIVOT、CROSS APPLY、LATERAL VIEW、PIVOT等函数，以及动态SQL和crosstab函数，适用于不同数据库系统。
sql如何行转列，列转行
本文介绍SQL中实现行转列和列转行的具体方法，包括使用case when语句结合max或sum函数，以及利用PIVOT和UNPIVOT函数简化操作。这些技巧有助于提高SQL查询效率。
SQL 行转列，列转行
本文介绍如何使用SQL实现数据从长表到宽表的行转列，以及从宽表到长表的列转行操作。具体地，行转列采用sum+if函数组合，通过条件聚合实现；列转行则利用union关键字进行多表纵向拼接。
掌握SQL中列转行的两种方法
本文聚焦SQL行列转换，介绍了使用UNION ALL结合CASE语句、PIVOT关键字等实现列转行的方法，阐述了ROLLUP在列转行中的应用，对比了不同数据库系统的行列转换方式，还说明了列转行在数据分析与报表制作中的重要性及应用实例，助开发者高效处理数据。
MS-SQL Server

34,876

社区成员

254,640

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章