MS-SQL行转列问题

newnazi 2016-05-30 09:15:47
已知表

ID , LinkCode ,BarCode , LinkTime , CodeType ，IsComplete
1, 111, 111 , getdate() , 1 , 0
2, 111, 222 , getdate() , 2 , 0
3, 111, 333 , getdate() , 3 , 0
4, aaa, aaa , getdate() , 1 , 1
5, aaa, bbb , getdate() , 2 , 1
6, aaa, ccc , getdate() , 3 , 1
7, aaa, ddd , getdate() , 4 , 1

查询结果如下

ID , 1 , 2 , 3 , 4 , LinkTime ， IsComplete

1 ，111 ， 222 , 333 , 4444 ，2016-06-01 8:00:00 ， 0
2 ，aaa , bbb , ccc , ddd , 2016-04-01 8:00:00 , 1


LinkCode 代表 每组BarCode 的连接关系 CodeType表示类型也是转换后的列名 当4种类型凑齐IsComplete=1表示完成

...全文
798 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
OwenZeng_DBA 2018-01-25
  • 打赏
  • 举报
 回复
引用 10 楼 newnazi 的回复:
大神们还有什么好的方法建议？？
怎么在很老的帖子里面回，有问题的话，可以单独发一个帖子，会有人回你的
newnazi 2018-01-25
  • 打赏
  • 举报
 回复
大神们还有什么好的方法建议？？
newnazi 2016-05-30
  • 打赏
  • 举报
 回复
引用 6 楼 roy_88 的回复:
LinkTime--不分组取最大值时，可这样用 
SELECT 
[LinkCode],[1],[2],[3],[4],[LinkTime],[IsComplete]
FROM (SELECT [LinkCode],[BarCode],[LinkTime]=MAX([LinkTime])OVER(PARTITION BY [LinkCode],[IsComplete]),[CodeType],[IsComplete] FROM #T ) AS a
PIVOT(MAX([BarCode]) FOR [CodeType] IN([1],[2],[3],[4])) AS b
你这个每组 查询结果为四行 不是有一行 2016-05-19 10:52:30 111 111 NULL NULL NULL 1 2016-05-19 10:53:02 111 NULL NULL 222 NULL 1 2016-05-19 10:55:42 111 NULL 333 NULL NULL 1 2016-05-19 11:03:04 111 NULL NULL NULL 444 1
Lightning-McQueen 2016-05-30
  • 打赏
  • 举报
 回复
引用 6 楼 roy_88 的回复:
LinkTime--不分组取最大值时，可这样用 
SELECT 
[LinkCode],[1],[2],[3],[4],[LinkTime],[IsComplete]
FROM (SELECT [LinkCode],[BarCode],[LinkTime]=MAX([LinkTime])OVER(PARTITION BY [LinkCode],[IsComplete]),[CodeType],[IsComplete] FROM #T ) AS a
PIVOT(MAX([BarCode]) FOR [CodeType] IN([1],[2],[3],[4])) AS b
一转眼就是10年 http://bbs.csdn.net/topics/90148521
中国风 2016-05-30
  • 打赏
  • 举报
 回复
LinkTime--不分组取最大值时，可这样用 
SELECT 
[LinkCode],[1],[2],[3],[4],[LinkTime],[IsComplete]
FROM (SELECT [LinkCode],[BarCode],[LinkTime]=MAX([LinkTime])OVER(PARTITION BY [LinkCode],[IsComplete]),[CodeType],[IsComplete] FROM #T ) AS a
PIVOT(MAX([BarCode]) FOR [CodeType] IN([1],[2],[3],[4])) AS b
唐诗三百首 2016-05-30
  • 打赏
  • 举报
 回复 

create table 已知表
(ID int,
 LinkCode varchar(10),
 BarCode varchar(10),   
 LinkTime datetime,
 CodeType int,
 IsComplete int)

insert into 已知表
 select 1, '111', '111', getdate() , 1 , 0 union all
 select 2, '111', '222', getdate() , 2 , 0 union all
 select 3, '111', '333', getdate() , 3 , 0 union all
 select 4, 'aaa', 'aaa', getdate() , 1 , 1 union all
 select 5, 'aaa', 'bbb', getdate() , 2 , 1 union all
 select 6, 'aaa', 'ccc', getdate() , 3 , 1 union all
 select 7, 'aaa', 'ddd', getdate() , 4 , 1


declare @tsql varchar(6000)

select @tsql=isnull(@tsql+',','')
                     +'max(case when CodeType='+rtrim(number)+' then BarCode else null end) ['+rtrim(number)+'] '
  from master.dbo.spt_values
  where type='P' and number>=1
  and number<=(select top 1 count(1) from 已知表 group by LinkCode order by count(1) desc)

select @tsql='select ID=row_number() over(order by getdate()),'+@tsql+','
                    +'            LinkTime=max(LinkTime),
                                   IsComplete=case when exists(select 1 from 已知表 u where u.LinkCode=t.LinkCode and u.IsComplete=0) then 0 else 1 end
                         from 已知表 t
                         group by t.LinkCode '

exec(@tsql)

/*
ID                   1          2          3          4          LinkTime                IsComplete
-------------------- ---------- ---------- ---------- ---------- ----------------------- -----------
1                    111        222        333        NULL       2016-05-30 09:46:40.547 0
2                    aaa        bbb        ccc        ddd        2016-05-30 09:46:40.547 1

(2 row(s) affected)
*/
中国风 2016-05-30
  • 打赏
  • 举报
 回复
或用pivot 

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[LinkCode] nvarchar(23),[BarCode] nvarchar(23),[LinkTime] DATETIME,[CodeType] int,[IsComplete] int)
Insert #T
select 1,N'111',N'111',getdate(),1,0 union all
select 2,N'111',N'222',getdate(),2,0 union all
select 3,N'111',N'333',getdate(),3,0 union all
select 4,N'aaa',N'aaa',getdate(),1,1 union all
select 5,N'aaa',N'bbb',getdate(),2,1 union all
select 6,N'aaa',N'ccc',getdate(),3,1 union all
select 7,N'aaa',N'ddd',getdate(),4,1
GO
SELECT 
[LinkCode],[1],[2],[3],[4],[LinkTime],[IsComplete]
FROM (SELECT [LinkCode],[BarCode],[LinkTime],[CodeType],[IsComplete] FROM #T ) AS a
PIVOT(MAX([BarCode]) FOR [CodeType] IN([1],[2],[3],[4])) AS b

/*
LinkCode	1	2	3	4	LinkTime	IsComplete
111	111	222	333	NULL	2016-05-30 09:56:06.653	0
aaa	aaa	bbb	ccc	ddd	2016-05-30 09:56:06.653	1
*/
中国风 2016-05-30
  • 打赏
  • 举报
 回复 
use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[LinkCode] nvarchar(23),[BarCode] nvarchar(23),[LinkTime] DATETIME,[CodeType] int,[IsComplete] int)
Insert #T
select 1,N'111',N'111',getdate(),1,0 union all
select 2,N'111',N'222',getdate(),2,0 union all
select 3,N'111',N'333',getdate(),3,0 union all
select 4,N'aaa',N'aaa',getdate(),1,1 union all
select 5,N'aaa',N'bbb',getdate(),2,1 union all
select 6,N'aaa',N'ccc',getdate(),3,1 union all
select 7,N'aaa',N'ddd',getdate(),4,1
GO
DECLARE @Sql NVARCHAR(max)=''
Select @Sql=@Sql+','+QUOTENAME([CodeType])+'=max(CASE WHEN [CodeType]='+QUOTENAME([CodeType],'''')+' THEN BarCode  else '''' END)' FROM #T GROUP BY [CodeType]
EXEC('SELECT [LinkCode]'+@Sql+',[LinkTime],[IsComplete] FROM #T GROUP BY [LinkCode],[LinkTime],[IsComplete]')

/*
LinkCode	1	2	3	4	LinkTime	IsComplete
111	111	222	333		2016-05-30 09:51:52.373	0
aaa	aaa	bbb	ccc	ddd	2016-05-30 09:51:52.373	1
*/
newnazi 2016-05-30
  • 打赏
  • 举报
 回复
引用 1 楼 ap0405140 的回复:
请问希望结果中的"4444"是怎么来的?
写错了 ID , 1 , 2 , 3 , 4 , LinkTime ， IsComplete 1 ，111 ， 222 , 333 , null ，2016-06-01 8:00:00 ， 0 2 ，aaa , bbb , ccc , ddd , 2016-04-01 8:00:00 , 1
唐诗三百首 2016-05-30
  • 打赏
  • 举报
 回复
请问希望结果中的"4444"是怎么来的?
中国风 2016-05-30
  • 打赏
  • 举报
 回复
引用 8 楼 newnazi 的回复:
你这个每组 查询结果为四行 不是有一行 2016-05-19 10:52:30 111 111 NULL NULL NULL 1 2016-05-19 10:53:02 111 NULL NULL 222 NULL 1 2016-05-19 10:55:42 111 NULL 333 NULL NULL 1 2016-05-19 11:03:04 111 NULL NULL NULL 444 1
怎样会不一样？检查你的数据和方法，以下再贴测试结果 

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[LinkCode] nvarchar(23),[BarCode] nvarchar(23),[LinkTime] DATETIME,[CodeType] int,[IsComplete] int)
Insert #T
select 1,N'111',N'111',getdate(),1,0 ;INSERT #T
select 2,N'111',N'222',getdate(),2,0 ;INSERT #T
select 3,N'111',N'333',getdate(),3,0 ;INSERT #T
select 4,N'aaa',N'aaa',getdate(),1,1 ;INSERT #T
select 5,N'aaa',N'bbb',getdate(),2,1 ;INSERT #T
select 6,N'aaa',N'ccc',getdate(),3,1 ;INSERT #T
select 7,N'aaa',N'ddd',getdate(),4,1
GO
SELECT 
[LinkCode],[1],[2],[3],[4],[LinkTime],[IsComplete]
FROM (SELECT [LinkCode],[BarCode],[LinkTime]=MAX([LinkTime])OVER(PARTITION BY [LinkCode],[IsComplete]),[CodeType],[IsComplete] FROM #T ) AS a
PIVOT(MAX([BarCode]) FOR [CodeType] IN([1],[2],[3],[4])) AS b
/*
LinkCode	1	2	3	4	LinkTime	IsComplete
111	111	222	333		2016-05-30 09:51:52.373	0
aaa	aaa	bbb	ccc	ddd	2016-05-30 09:51:52.373	1
*/
SQL行转列、列转行（SQL Server版）
在SQL Server中使用SQL实现行转列、列转行，可以使用多种方法，在SQL 2005以前可以使用case when then...语句，但这种方法的问题在于列举的列名要写死，如果列名很多，case when 语句会很长，并不优雅。所以需要SQL Server提供新的语句能够实现。在2005版本就推出了pivot/unpivot关键字，可以方便的实现。这方面的资料已经很多，这里提供一个简单的...
实战复盘：内网环境入侵ms-SQL数据库
web服务器、ms-SQL服务器、PC客户端在同一个网络中，是一种危险的网络结构，入侵ms-SQL服务器，非常容易
T-SQL的简介以及基本语法
目录一、T-SQL介绍二、T-SQL编程基础1.标识符2.数据类型2.1 系统数据类型2.2 程序中的数据类型3.表达式3.1 变量3.2 函数3.3 运算符 一、T-SQL介绍 百度介绍： T-SQL 即 Transact-SQL，是 SQL 在 Microsoft SQL Server 上的增强版，它是用来让应用程序与 SQL Server 沟通的主要语言。T-SQL 提供标准 SQL 的 DDL 和 DML 功能，加上延伸的函数、系统预存程序以及程式设计结构(例如 IF 和 WHILE)让程式设
重温SQL行转列，性能又双叒提升了
行列转换的相关知识做了一个总结，希望对 大家有所帮助
ms-sql server sql 把逗号分隔的字符串分开
ms-sql server sql 把逗号分隔的字符串分开
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章