34,587
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int, row int ,value varchar(10))
go
insert tb SELECT
1, 1 ,'a' UNION ALL SELECT
1, 2, 'b' UNION ALL SELECT
2, 1, 'c' UNION ALL SELECT
3, 2, 'd'
go
select * from tb
pivot
(
max(value) for row in([1],[2])
) p
/*
id 1 2
----------- ---------- ----------
1 a b
2 c NULL
3 NULL d
(3 行受影响)
*/
--借T_mac的数据以用
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int, row int ,value varchar(10))
go
insert tb SELECT
1, 1 ,'a' UNION ALL SELECT
1, 2, 'b' UNION ALL SELECT
2, 1, 'c' UNION ALL SELECT
3, 2, 'd'
go
select id,
value1=max(case row when 1 then value else null end),
value2=max(case row when 2 then value else null end)
from tb
group by id
drop table tb
/*id value1 value2
----------- ---------- ----------
1 a b
2 c NULL
3 NULL d
警告: 聚合或其他 SET 操作消除了空值。
(3 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-07 12:47:59
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[row] int,[value] varchar(1))
insert [tb]
select 1,1,'a' union all
select 1,2,'b' union all
select 2,1,'c' union all
select 3,2,'d'
--------------开始查询--------------------------
select
[id],
value1=max(case row when 1 then [value] else null end),
value2=max(case row when 2 then [value] else null end) from [tb]
group by
[id]
----------------结果----------------------------
/*id value1 value2
----------- ------ ------
1 a b
2 c NULL
3 NULL d
(所影响的行数为 3 行)
警告: 聚合或其它 SET 操作消除了空值。
*/
declare @t table(id int ,row int,value char(1))
insert @t
select 1 ,1 ,'a' union all
select 1 ,2 ,'b' union all
select 2 ,1 ,'c' union all
select 3 ,2 ,'d'
select * from @t
select isnull(A.id,B.id) id,A.value value1,B.value value2 from
(select * from @t where row=1) A
full join
(select * from @t where row=2) B
on A.id=B.id
order by id
id value1 value2
----------- ------ ------
1 a b
2 c NULL
3 NULL d
declare @tb table(id int ,row int, value varchar(5))
insert @tb select 1, 1 ,'a'
insert @tb select 1, 2 ,'b'
insert @tb select 2, 1 ,'c'
insert @tb select 3, 2 ,'d'
select a.id,
value1=max(case when a.row=1 then a.value when b.row=1 then b.value end),
value2=max(case when a.row=2 then a.value when b.row=2 then b.value end)
from @tb a ,@tb b where a.id=b.id
group by a.id
order by a.id
/*
id value1 value2
----------- ------ ------
1 a b
2 c NULL
3 NULL d
(所影响的行数为 3 行)
*/
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int,row int,[value] varchar(1))
insert into [tb]
select 1,1,'a' union all
select 1,2,'b' union all
select 2,1,'c' union all
select 3,2,'d'
select id,value1=max(case row when 1 then [value] else null end),
value2=max(case row when 2 then [value] else null end) from [tb]
group by id
--结果:
id value1 value2
----------- ------ ------
1 a b
2 c NULL
3 NULL d
-- =========================================
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int, row int ,value varchar(10))
go
insert tb SELECT
1, 1 ,'a' UNION ALL SELECT
1, 2, 'b' UNION ALL SELECT
2, 1, 'c' UNION ALL SELECT
3, 2, 'd'
go
select distinct
ID,
value1=(select value from tb where t.id=id and row=1),
value2=(select value from tb where t.id=id and row=2)
from tb t
go
(4 行受影响)
ID value1 value2
----------- ---------- ----------
1 a b
2 c NULL
3 NULL d
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int, row int ,value varchar(10))
go
insert tb SELECT
1, 1 ,'a' UNION ALL SELECT
1, 2, 'b' UNION ALL SELECT
2, 1, 'c' UNION ALL SELECT
3, 2, 'd'
go
select * from tb
select distinct id,value1=(select value from tb where row=1 and id =t.id),
value2=(select value from tb where row = 2 and id = t.id)
from tb t
/**
id value1 value2
----------- ---------- ----------
1 a b
2 c NULL
3 NULL d
(所影响的行数为 3 行)
**/
select
[id],
max(case when row=1 then [value] else null end) AS VALUE1,
max(case when row=2 then [value] else null end) AS VALUE2
from [tb]
group by
[id]
declare @a table(id int, row int,[value] varchar(12))
insert @a select
1, 1, 'a' union all select
1, 2, 'b' union all select
2, 1, 'c' union all select
3, 2, 'd'
select id ,datarow1=max(case when row=1 then [value] when row<>1 and row<>2 then null end),
datarow2=max(case when row=2 then [value] when row<>1 and row<>2 then null end)
from @a
group by id
id datarow1 datarow2
----------- ------------ ------------
1 a b
2 c NULL
3 NULL d
警告: 聚合或其他 SET 操作消除了空值。
(3 行受影响)