22,207
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-19 10:44:04
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([FNumber] nvarchar(32),[FISeries] nvarchar(6))
insert [huang]
select 'M.QZ.AM.TL.00001',N'内牙' union all
select 'B.QZ.AM.TL.00024',N'内牙' union all
select 'B.QT.AM.TL.00022',N'内牙' union all
select 'P.BJ.PL.00008',N'平螺母' union all
select 'P.CY.QW.00001',N'球碗' union all
select 'P.CY.QW.00002',N'球碗'
--------------生成数据--------------------------
SELECT ISNULL([内牙],'\')[内牙],ISNULL([平螺母],'\')[平螺母]
FROM
(SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())id, fnumber AS N'内牙'
FROM huang
WHERE [FISeries]=N'内牙')a full JOIN (SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())id, fnumber AS N'平螺母'
FROM huang
WHERE [FISeries]=N'平螺母') b ON a.id=b.id
----------------结果----------------------------
/*
内牙 平螺母
-------------------------------- --------------------------------
M.QZ.AM.TL.00001 P.BJ.PL.00008
B.QZ.AM.TL.00024 \
B.QT.AM.TL.00022 \
*/
动态的话我只能做到这里:
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-19 10:44:04
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([FNumber] nvarchar(32),[FISeries] nvarchar(6))
insert [huang]
select 'M.QZ.AM.TL.00001',N'内牙' union all
select 'B.QZ.AM.TL.00024',N'内牙' union all
select 'B.QT.AM.TL.00022',N'内牙' union all
select 'P.BJ.PL.00008',N'平螺母' union all
select 'P.CY.QW.00001',N'球碗' union all
select 'P.CY.QW.00002',N'球碗'
--------------生成数据--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([FISeries])+'=(case when [FISeries]=N'+quotename([FISeries],'''')+' then FNumber else ''\'' end)'
from [huang] group by [FISeries]
SET @s=SUBSTRING(@s,2,LEN(@s))
exec('select '+@s+' from [huang] ')
----------------结果----------------------------
/*
内牙 平螺母 球碗
-------------------------------- -------------------------------- --------------------------------
M.QZ.AM.TL.00001 \ \
B.QZ.AM.TL.00024 \ \
B.QT.AM.TL.00022 \ \
\ P.BJ.PL.00008 \
\ \ P.CY.QW.00001
\ \ P.CY.QW.00002
*/