BarCode WorkShop IntGroup IntCount
A 1 1 1
A 2 1 2
A 3 1 3
B 1 2 1
B 2 2 2
C 1 3 1
C 2 3 2
C 3 3 3
D 1 4 1
通过以上结果集如何编写,得到以下结果集:
BarCode WorkShop BarCode WorkShop BarCode WorkShop
A 1 A 2 A 3
B 1 B 2 Null Null
C 1 C 2 C 3
D 1 Null Null Null Null
A 1 1 1
A 2 1 2
A 3 1 3
B 1 2 1
B 2 2 2
C 1 3 1
C 2 3 2
C 3 3 3
D 1 4 1
通过以上结果集如何编写,得到以下结果集:
BarCode WorkShop BarCode WorkShop BarCode WorkShop
A 1 A 2 A 3
B 1 B 2 Null Null
C 1 C 2 C 3
D 1 Null Null Null Null
a.barcode,
a.workshop,
b.barcode,
b.workshop,
c.barcode,
c.workshop
from
tb a
left join tb b on a.BarCode=b.BarCode and a.workshop+1=b.workshop
left join tb c on a.BarCode=c.BarCode and a.workshop+2=c.workshop
-- Author: T.O.P
-- Create date: 20091127
-- Version: SQL SERVER 2000
-- =============================================
declare @TB table([BarCode] varchar(1),[WorkShop] int,[IntGroup] int,[IntCount] int)
insert @TB
select 'A',1,1,1 union all
select 'A',2,1,2 union all
select 'A',3,1,3 union all
select 'B',1,2,1 union all
select 'B',2,2,2 union all
select 'C',1,3,1 union all
select 'C',2,3,2 union all
select 'C',3,3,3 union all
select 'D',1,4,1select BarCode, WorkShop, (SELECT BarCode FROM @TB WHERE A.BarCode=BarCode AND WorkShop=2) AS BarCode
, (SELECT WorkShop FROM @TB WHERE A.BarCode=BarCode AND WorkShop=2) AS WorkShop
, (SELECT BarCode FROM @TB WHERE A.BarCode=BarCode AND WorkShop=3) AS BarCode
, (SELECT WorkShop FROM @TB WHERE A.BarCode=BarCode AND WorkShop=3) AS WorkShop
from @TB a
where WorkShop=1
--测试结果:
/*
BarCode WorkShop BarCode WorkShop BarCode WorkShop
------- ----------- ------- ----------- ------- -----------
A 1 A 2 A 3
B 1 B 2 NULL NULL
C 1 C 2 C 3
D 1 NULL NULL NULL NULL(所影响的行数为 4 行)*/这样???
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([BarCode] varchar(1),[WorkShop] int,[IntGroup] int,[IntCount] int)
insert [tb]
select 'A',1,1,1 union all
select 'A',2,1,2 union all
select 'A',3,1,3 union all
select 'B',1,2,1 union all
select 'B',2,2,2 union all
select 'C',1,3,1 union all
select 'C',2,3,2 union all
select 'C',3,3,3 union all
select 'D',1,4,1
---查询---
select
a.barcode,
a.workshop,
b.barcode,
b.workshop,
c.barcode,
c.workshop
from
tb a
left join tb b on a.BarCode=b.BarCode and a.workshop+1=b.workshop
left join tb c on a.BarCode=c.BarCode and a.workshop+2=c.workshop
where a.workshop=1---结果---
barcode workshop barcode workshop barcode workshop
------- ----------- ------- ----------- ------- -----------
A 1 A 2 A 3
B 1 B 2 NULL NULL
C 1 C 2 C 3
D 1 NULL NULL NULL NULL(所影响的行数为 4 行)
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-11-27 18:07:54
-------------------------------------
--> 生成测试数据: #tb
IF OBJECT_ID('tempdb.dbo.#tb') IS NOT NULL DROP TABLE #tb
CREATE TABLE #tb (BarCode varchar(1),WorkShop int,IntGroup int,IntCount int)
INSERT INTO #tb
SELECT 'A',1,1,1 UNION ALL
SELECT 'A',2,1,2 UNION ALL
SELECT 'A',3,1,3 UNION ALL
SELECT 'B',1,2,1 UNION ALL
SELECT 'B',2,2,2 UNION ALL
SELECT 'C',1,3,1 UNION ALL
SELECT 'C',2,3,2 UNION ALL
SELECT 'C',3,3,3 UNION ALL
SELECT 'D',1,4,1--SQL查询如下:DECLARE @s varchar(8000);
SET @s = '';SELECT @s=@s+',MAX(CASE WHEN IntCount='+RTRIM(number+1)
+' THEN BarCode END) AS BarCode'+RTRIM(number+1)
+',MAX(CASE WHEN IntCount='+RTRIM(number+1)
+' THEN WorkShop END) AS WorkShop'+RTRIM(number+1)
FROM master.dbo.spt_values
WHERE type = 'P'
AND number < (SELECT MAX(IntCount) FROM #tb);
SET @s = STUFF(@s,1,1,'');EXEC('SELECT '+@s+' FROM #tb GROUP BY IntGroup,BarCode')