sql 两行数据合成一行
如：
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 请教sql高手
[解决办法]
----------------------------
-- 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
*/
--静态就这么简单
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
