XuGang

--(1)
--====================================================================
--拆分
DECLARE @s VARCHAR(100)
SET @s='a,b,c,dd,ee,f,aa,a,aa,f'

--常規做法(sql2000常用),以一split函數拆分串為表類型結構,如
--SELECT * FROM dbo.split(@s,',') a
--當然，也可能是循環去拆分,或者以一輸助表的identity列利用charindex等函數拿identity列值與','的位置匹配實現拆分
--這些做法，roy_88及本人以前都整理過，不再累贅，可見推薦貼。即便 是xml法，也貼過多次，下面一筆帶過

--XML做法:
SELECT b.v FROM
    (SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) a   --將字串","換換為"</r><r>"并前后拼上<r>,</r>以用來構造xml串
CROSS APPLY
    (SELECT v=t.x.value('.','VARCHAR(10)') FROM a.x.nodes('//r') AS t(x) ) b  --使用 xml.nodes函數將xml串拆分為行
/*
a
b
c
dd
ee
f
aa
a
aa
f
*/


--(2)
--====================================================================
--去重,@s中出現的元素，重復的只要一個,希望結果為 'a,b,c,dd,ee,f'
--常規做法，循環或函數,或臨時表拆后distinct
--XML做法:
--a.在(1)的基礎上進行

;WITH fc AS   --定義cte命名,將@s轉換為一個表結構
(
    SELECT DISTINCT b.v v
            FROM
                (SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) a
            CROSS APPLY
                (SELECT v=t.x.value('.','VARCHAR(10)') FROM a.x.nodes('//r') AS t(x) ) b
)
--對這個表利用xml方法進行行值拼接
SELECT STUFF(b.v.value('/r[1]','varchar(100)'),1,1,'')
    FROM
    (SELECT v=(SELECT ',' + v FROM fc FOR XML PATH(''),ROOT('r'),TYPE)) b
/*
a,aa,b,c,dd,ee,f
*/

--b FLWOR語句 + T-SQL組合:
SELECT STUFF(v,1,1,'') FROM
    (SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) a
CROSS APPLY
    (SELECT x=(SELECT t.x.value('.','varchar(10)') v,idx=ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM a.x.nodes('//r') AS t(x) FOR XML PATH('r'),TYPE)) b --利用row_number得到唯一idx
CROSS APPLY
    (SELECT v=CAST(b.x.query('for $r in //r where count(//r[v=$r/v and idx<$r/idx])=0 return concat(",",xs:string($r/v[1]))') AS VARCHAR(MAX))) c  --類似count計數法，取得v相同的節點集idx值最小的節點,原型為:
--SELECT * FROM tb a WHERE 1>(SELECT COUNT(*) FROM tb WHERE v=a.v AND id<a.id)
/*
a ,b ,c ,dd ,ee ,aa ,f
*/


--c distinct-values
SELECT REPLACE(v,' ',',') FROM
    (SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) a
CROSS APPLY
    (SELECT CAST(a.x.query('distinct-values(//r)') AS VARCHAR(MAX)) v) b  --直接調用distinct-values函數來操作
/*
a,b,c,dd,ee,f,aa
*/


-- 導入去重, last() , position()

DECLARE   @doc  xml
SET   @doc   ='<?xml version="1.0" encoding="gb2312" ?>
<employees>
    <employee>
        <empid>e0001</empid>
        <name>蕭峰</name>
    </employee>
    <employee>
        <empid>e0002</empid>
        <name>段譽</name>
    </employee>
    <employee>
        <empid>e0003</empid>
        <name>王語嫣</name>
    </employee>
    <employee>
        <empid>e0003</empid>
        <name>張無忌</name>
    </employee>
</employees>
'
create table people2 
( 
    personid varchar(10)  primary key ,
    name varchar(20) 
 )

INSERT people2
SELECT DISTINCT b.* FROM
    (SELECT x = @doc.query('for $e in //employee  return  //employee[empid = $e/empid][last()]')) a  --FLWOR時,用當前節點去//emploee節點集中找節點集中empid等于當前節點的empid, 在找到的集合中取最后一個利用last()函數
CROSS APPLY
    (SELECT id=t.x.value('empid[1]','varchar(100)'),name=t.x.value('name[1]','varchar(100)') FROM a.x.nodes('//employee') AS t(x)) b

SELECT * FROM people2
/*
e0001    蕭峰
e0002    段譽
e0003    張無忌
*/
GO
drop table people2 
GO
--同組一選多，也可應用此方法，不過沒有必要，就不再累贅了。


--(3)
--====================================================================
--列名,列值相關
--a,按行聚合
declare @t table(Sname nvarchar(5),  V1 float,    V2 float,    V3 float,      V4 float,    V5 float,      V6 float) 
insert @t select N'張三',    0.11 , 0.21 , 0.29,  0.32 ,   0.11,    0.08 
insert @t select N'李四',    0.01 , 0.61 , 0.21,  0.73 ,   0.21,    0.12 
insert @t select N'張五',    0.31 , 0.21 , 0.23,  0.33 ,   0.91,    0.65 
insert @t select N'張六',    0.59 , 0.11,  0.26,  0.13,    0.01,    0.15 

select b.* from
    (select x=cast((select * from @t for xml path('r')) as xml)) a
cross apply
    (
        select name=x.query('./Sname/text()'),v=x.query('max(./*[local-name(.)!="Sname"])') from a.x.nodes('//r') as t(x)  
        --r為二級節點(因為文檔本身無根節點，即為每項的頂級節點)即為一個r節點表示一條記錄. r下級節點，每個表示一個列，因為列名未知，所以用/*匹配所有節點，因為name為區別列，不參與聚合運算，故用local-name取得來過濾
    ) b

/*
張三    0.32
李四    0.73
張五    0.91
張六    0.59
*/

--b ,由值引到取列
if not object_id('T1') is null
    drop table T1
GO
Create table T1([tId] int,[tName] nvarchar(4))
Insert T1
select 1,N'zhao' union all
select 2,N'qian' union all
select 3,N'sun'
Go
--> --> 借且(Roy)生成測試數據
 
if not object_id('T2') is null
    drop table T2
Go
Create table T2([tId] int,[zhao] nvarchar(1),[qian] nvarchar(1),[sun] nvarchar(1))
Insert T2
select 1,N'a',N'b',N'c' union all
select 2,N'd',N'e',N'f' union all
select 3,N'g',N'h',N'i'
Go


SELECT c.tid,c.tName,v FROM t1 c
CROSS APPLY
    (SELECT x=(SELECT * FROM t2 WHERE tid=c.tid FOR XML PATH('r'),TYPE)) a
CROSS APPLY
    (SELECT v=t.x.query('./*[local-name(.)=xs:string(sql:column("c.tName")) ]/text()') 
        FROM a.x.nodes('//r') AS t(x)
    ) b

/*
1    zhao    a
2    qian    e
3    sun    i
*/


--c, 列名，列值，與系統表 

CREATE TABLE tb(f1 INT,f2 INT,x INT,z INT,d INT,ex INT,dd INT,vv INT) 
INSERT tb SELECT 1,2,3,5,11,3,2423,33 
GO 
SELECT * FROM tb 
GO 
SELECT name,v FROM
  ( SELECT name FROM sys.columns WHERE object_id=object_id('tb','u') ) a 
CROSS JOIN
  (SELECT x=(SELECT * FROM tb FOR XML PATH('r'),TYPE)) b 
CROSS APPLY
 (SELECT v=t.x.query('./*[local-name(.)=xs:string(sql:column("a.name")) ]/text()') FROM b.x.nodes('//r') AS t(x) ) c 
/*
f1    1
f2    2
x    3
z    5
d    11
ex    3
dd    2423
vv    33
*/
GO 
DROP TABLE tb
GO

--(4)
--一些綜合計算
--以下表 ta.a值 yyyymmdd-yyyymmdd表連續時間段，","表單個日期
If object_id('ta','u') is not null 
    Drop table ta
Go
Create table ta(a varchar(100))
Go
Insert into ta
select '1 | |20080101-20080911' 
union all
select '2 | |20080101，20080201，20080301，20080515，20080808'
union all
select '3 | |20080101，20080201，20080301，20080515，20081108'
Go

declare @s varchar(8)
select @s= convert(varchar(8),getdate(),112)

select stuff(replace(replace(cast(x as varchar(1000)),'</item><item>',case when type='1' then '-' else '，' end),'</item>',''),1,6,type + ' | |') a
    from
    (
        select left(a,1) type, 
            cast(
                    '<item>' 
                    + 
                    replace(
                        stuff(a,1,5,''),
                        case when left(a,1)=1 then '-' else '，' end,
                        '</item><item>'
                        )
                    + 
                    '</item>'
                AS XML
                ) x
        from ta
    ) base

    where x.value('
            if (sql:column("base.type")="1") then
                if(
                    (/item/text())[1]<sql:variable("@s")
                    and
                    (/item/text())[2]>sql:variable("@s")
                )
                then 1
                else 0
            else
                count(//item[text()>sql:variable("@s")])
            '
            ,
            'int'
            )>0
go