有个表A：最多只到A05
A00 A01 A02 A03 A04 A05
-------------------------------------------
P01 01 02 03 04 05
P02 01 02
P03 01
P04 01 02 03 04
....
现在想拼凑一下，比如P01有三个01,02,03，那么我想得到三条记录，想得到如下表的格式：
表B:
B00 B01
----------------------------------
P01 P01_01
P01 P01_02
P01 P01_03
P01 P01_04
P01 P01_05
P02 P02_01
P02 P02_02
P03 P03_01
P04 P04_01
P04 P04_02
P04 P04_03
P04 P04_04
......
------解决方案--------------------
create table 表A
(A00 varchar(10),
A01 varchar(10),A02 varchar(10),A03 varchar(10),A04 varchar(10),A05 varchar(10))
insert into 表A
select 'P01','01','02','03','04','05' union all
select 'P02','01','02','','','' union all
select 'P03','01','','','','' union all
select 'P04','01','02','03','04',''
select A00 'B00',
A00+'_'+v 'B01'
from 表A a
unpivot(v for c in([A01],[A02],[A03],[A04],[A05])) u
where v!=''
/*
B00 B01
---------- ---------------------
P01 P01_01
P01 P01_02
P01 P01_03
P01 P01_04
P01 P01_05
P02 P02_01
P02 P02_02
P03 P03_01
P04 P04_01
P04 P04_02
P04 P04_03
P04 P04_04
(12 row(s) affected)
*/
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-09 10:17:10
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([A00] varchar(3),[A01] varchar(2),[A02] varchar(2),[A03] varchar(2),[A04] varchar(2),[A05] varchar(2))
insert [A]
select 'P01','01','02','03','04','05' union all
select 'P02','01','02',null,null,null union all
select 'P03','01',null,null,null,null union all
select 'P04','01','02','03','04',null
--------------开始查询--------------------------
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [A00] as [B00],[B01]=[A00]+''_''+'+quotename(Name)
+' from [A]'
from syscolumns where ID=object_id('A') and Name not in('A00') --排除不转换的列
order by Colid
exec('select * from ('+@s+')t where [B01] is not null order by [B00]')--增加一个排序
----------------结果----------------------------
/*
B00 B01
---- ------
P01 P01_01
P01 P01_02
P01 P01_03
P01 P01_04
P01 P01_05
P02 P02_02
P02 P02_01
P03 P03_01
P04 P04_01
P04 P04_02
P04 P04_03
P04 P04_04
*/
------解决方案--------------------
if object_ID('tempdb..#temp','U') IS NOT NULL DROP TABLE #temp
CREATE TABLE #temp
(
A00 VARCHAR(3)
,A01 VARCHAR(2)
,A02 VARCHAR(2)