22,206
社区成员
发帖
与我相关
我的任务
分享
CSDN搞什么鬼,给我自动加了个<br>进去
--静态就这么简单
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
----------------------------------------------------------------
-- 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
*/
----------------------------------------------------------------
-- 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
*/
----------------------------------------------------------------
-- 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
*/