sql 两行数据合成一行

天二天 2013-05-16 04:39:33
如:
s_id goodID number
1 5 75
2 6 80
1 6 67
2 5 34
合成
s_id goodID(5)Number goodID(6)Number
1 75 67
2 34 80
...全文
249 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
天二天 2013-05-21
  • 打赏
  • 举报
回复
不错,多谢指教!
發糞塗牆 2013-05-16
  • 打赏
  • 举报
回复
因为我就姓黄
十一文 2013-05-16
  • 打赏
  • 举报
回复
引用 5 楼 DBA_Huangzj 的回复:
CSDN搞什么鬼,给我自动加了个<br>进去
抽风 抽风了
MrYangkang 2013-05-16
  • 打赏
  • 举报
回复
引用 6 楼 ZaoLianBuXiQi 的回复:
引用 5 楼 DBA_Huangzj 的回复:
CSDN搞什么鬼,给我自动加了个<br>进去
楼主,这类题总是可以看到你拿这个表[huang]回帖
说错啦,是版主
MrYangkang 2013-05-16
  • 打赏
  • 举报
回复
引用 5 楼 DBA_Huangzj 的回复:
CSDN搞什么鬼,给我自动加了个<br>进去
楼主,这类题总是可以看到你拿这个表[huang]回帖
發糞塗牆 2013-05-16
  • 打赏
  • 举报
回复
CSDN搞什么鬼,给我自动加了个<br>进去
MrYangkang 2013-05-16
  • 打赏
  • 举报
回复

--静态就这么简单
select s_id,
max(case when goodid = 5 then number end) good(5)number,
max(case when goodid = 6 then number end) good(6)number
from [huang] group by s_id
發糞塗牆 2013-05-16
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-16 16:45:14
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([s_id] int,[goodID] int,[number] int)
insert [huang]
select 1,5,75 union all
select 2,6,80 union all
select 1,6,67 union all
select 2,5,34
--------------开始查询--------------------------
--静态
select s_id,[goodID(5)Number]=max(CASE WHEN goodID=5 THEN number ELSE 0 END ) ,
[goodID(6)Number]=max(CASE WHEN goodID=6 THEN number ELSE 0 END )
from [huang]
GROUP BY s_id

--动态
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename('goodID('+CONVERT(CHAR(1),goodid)+')Number')+'=max(case when [goodID]='+quotename(goodID,'''')+' then [number] else 0 end)'
from [huang]
group BY goodID
exec('select [s_id]'+@s+' from [huang] group by s_id')
----------------结果----------------------------
/*
s_id goodID(5)Number goodID(6)Number
----------- --------------- ---------------
1 75 67
2 34 80

(2 行受影响)

s_id goodID(5)Number goodID(6)Number
----------- --------------- ---------------
1 75 67
2 34 80

*/
發糞塗牆 2013-05-16
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-16 16:45:14
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([s_id] int,[goodID] int,[number] int)
insert [huang]
select 1,5,75 union all
select 2,6,80 union all
select 1,6,67 union all
select 2,5,34
--------------开始查询--------------------------
--静态
select s_id,[goodID(5)Number]=max(CASE WHEN goodID=5 THEN number ELSE 0 END ) ,
[goodID(6)Number]=max(CASE WHEN goodID=6 THEN number ELSE 0 END )
from [huang]
GROUP BY s_id

--动态
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename('goodID('+CONVERT(CHAR(1),goodid)+')Number')+'=max(case when [goodID]='+quotename(goodID,'''')+' then [number] else 0 end)'
from [huang]
group BY goodID
exec('select [s_id]'+@s+' from [huang] group by s_id')
----------------结果----------------------------
/*
s_id goodID(5)Number goodID(6)Number
----------- --------------- ---------------
1 75 67
2 34 80

(2 行受影响)

s_id goodID(5)Number goodID(6)Number
----------- --------------- ---------------
1 75 67
2 34 80

*/
發糞塗牆 2013-05-16
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-05-16 16:45:14
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
--	Jun 17 2011 00:54:03 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([s_id] int,[goodID] int,[number] int)
insert [huang]
select 1,5,75 union all
select 2,6,80 union all
select 1,6,67 union all
select 2,5,34
--------------开始查询--------------------------

select s_id,[goodID(5)Number]=max(CASE WHEN goodID=5 THEN number ELSE 0 END ) ,
[goodID(6)Number]=max(CASE WHEN goodID=6 THEN number ELSE 0 END ) 
from [huang]
GROUP BY s_id
----------------结果----------------------------
/* 
s_id        goodID(5)Number goodID(6)Number
----------- --------------- ---------------
1           75              67
2           34              80
*/

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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