SQL 2008根据条件拆分列，sql2008拆分列

SQL 2008根据条件拆分列，sql2008拆分列

         在为人事局做报表过程中，遇到一个棘手的问题，客户要求把数据库中的一列数据根据条件分成多列。

   比如：数据库中有省份这么一列数据

   

   客户要求根据省份分类，河北省一列、北京市一列、天津市一列，剩下的为其他，目标效果如下：

   

   手工编写的SQL语句Version1.0版：

select   
  河北 = PARSENAME(provice,(charindex('河北',provice))) , 
  北京 = PARSENAME(provice,(charindex('北京',provice))) , 
  天津 = PARSENAME(provice,(charindex('天津',provice))) ,
  其他 =(select provice where charindex('河北',provice) = 0 
						  and charindex('北京',provice) = 0  
                          and charindex('天津',provice) = 0)
from a

        执行效果如下：

   
   可用户的需要只是想查询这个人是否在这个省份内，并不想知道具体是这个省的哪个城市，所以用replace函数对SQL语句进行了升级，SQL语句Version2.0版：

select   
  河北 =REPLACE(PARSENAME(provice,(charindex('河北',provice))),PARSENAME(provice,(charindex('河北',provice))),'√' ), 
  北京 =REPLACE(PARSENAME(provice,(charindex('北京',provice))),PARSENAME(provice,(charindex('北京',provice))),'√' ), 
  天津 =REPLACE(PARSENAME(provice,(charindex('天津',provice))),PARSENAME(provice,(charindex('天津',provice))),'√' ), 
  其他 =REPLACE((select provice where charindex('河北',provice) = 0 
						  and charindex('北京',provice) = 0  
                          and charindex('天津',provice) = 0),
                 (select provice where charindex('河北',provice)= 0 
						  and charindex('北京',provice) = 0  
                          and charindex('天津',provice) = 0),
                  '√')
from a

            执行效果如下：

    
    在解决问题过程中，发现网上对此类问题并没有很好的解决方案，所以在此把自己研究成果拿出来和大家分享下，希望能帮助大家解决开发过程中遇到的问题。

    

用SQL SERVER XML 功能来解决
----> 生成测试数据: @T
DECLARE @T TABLE (id INT,Col1 VARCHAR(50),Col2 VARCHAR(50))
INSERT INTO @T
SELECT 1,'卸货费','运输车队业务, 客服接单业务'
UNION ALL
SELECT 2,'报关费','报关业务、客服接单业务'

----SQL查询如下:

SELECT
T.id,
A.x.value('.','varchar(10)') AS code,
B.x.value('.','varchar(10)') AS code2
FROM (
SELECT *,doc = CONVERT(xml,'<v>'+REPLACE(code,',','</v><v>')+'</v>'),
doc2=CONVERT(xml,'<v>'+REPLACE(code2,',','</v><v>')+'</v>')
FROM @T
) AS T
CROSS APPLY doc.nodes('//v') AS A(x)
CROSS APPLY doc2.nodes('//v') AS B(x)
 

------------------------------------------------------------------------
-- author:jc_liumangtu(【DBA】小七)
-- date: 2010-03-26 09:37:30
-- version:
-- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
-- Oct 14 2005 00:33:37
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
------------------------------------------------------------------------use test
set nocount on
if object_id('test4','U') is not null
drop table test4
go
create table test4
(item int ,date char(8),type char(2),quty int)
insert into test4
select 1000 , '20100101', 'A' , 100 union all
select 2000 , '20100101' ,'B' , 200 union all
select 1000 , '20100101' ,'C' , 100 union all
select 2000 , '20100101' , 'D' , 100
select * from test4select item ,date
,max(case type when 'A' then quty else 0 end) [typeA]
,max(case type when 'B' then quty else 0 end) [typeB]
,max(case type when 'C' then quty else 0 end) [typeC]
,max(case type when 'D' then quty else 0 end) [typeD]
from test4 group by item,date
item date type quty
----------- -------- ---- -----------
1000 20100101 A 100
2000 20100101 B 200
1000 20100101 C 100
2000 20100101 D 100item date typeA typeB typeC typeD
----------- -------- ----------- ----------- ----------- -----------
1000 ......余下全文>>