Sql 分組插入臨時表中

[So_CooL]

表A中有一個字段為BeginDt的字段值格式是：

BeginDt字段的值有可能是包含多個日期值,值與值之間用','分割的，格式如下

2009-1-4,2009-2-3,2009-3-7
2009-4-4,2009-5-6,2009-7-12

怎么樣跟將數據轉換成如下格式：

第一列
select * from A where Convert(varchar(10),getdate(),121) ='2009-1-4'
select * from A where Convert(varchar(10),getdate(),121) ='2009-1-4'
select * from A where Convert(varchar(10),getdate(),121) ='2009-1-4'

第二列
select * from A where Convert(varchar(10),getdate(),121) ='2009-4-4'
select * from A where Convert(varchar(10),getdate(),121) ='2009-5-6'
select * from A where Convert(varchar(10),getdate(),121) ='2009-7-12'
并將數據插入到一個臨時表中，

[水族杰纶]

[Quote=引用楼主 So_CooL 的帖子:]
表A中有一個字段為BeginDt的字段值格式是：

BeginDt字段的值有可能是包含多個日期值,值與值之間用','分割的，格式如下

2009-1-4,2009-2-3,2009-3-7
2009-4-4,2009-5-6,2009-7-12

怎么樣跟將數據轉換成如下格式：

第一列
select * from A where Convert(varchar(10),getdate(),121) ='2009-1-4' --
select * from A where Convert(varchar(10),getdate(),121) ='2009-1-4'
select * from A where Convert(va…
[/Quote]
第一列是否有問題?

[So_CooL]

表A中有一個字段為BeginDt的字段值格式是：

BeginDt字段的值有可能是包含多個日期值,值與值之間用','分割的，格式如下

2009-1-4,2009-2-3,2009-3-7,
2009-4-4,2009-5-6,2009-7-12,

怎么樣跟將數據轉換成如下格式：

第一列
select * from A where Convert(varchar(10),getdate(),121) ='2009-1-4'
select * from A where Convert(varchar(10),getdate(),121) ='2009-1-4'
select * from A where Convert(varchar(10),getdate(),121) ='2009-1-4'

第二列
select * from A where Convert(varchar(10),getdate(),121) ='2009-4-4'
select * from A where Convert(varchar(10),getdate(),121) ='2009-5-6'
select * from A where Convert(varchar(10),getdate(),121) ='2009-7-12'
并將數據插入到一個臨時表中，

[So_CooL]

數據格式跟我的實例格式一樣。麻煩各位幫我看看，要怎么寫。

[liangCK]

[Quote=引用 4 楼 So_CooL 的回复:]
表A中有一個字段為BeginDt的字段值格式是：

BeginDt字段的值有可能是包含多個日期值,值與值之間用','分割的，格式如下

2009-1-4,2009-2-3,2009-3-7,
2009-4-4,2009-5-6,2009-7-12,

怎么樣跟將數據轉換成如下格式：

第一列
select * from A where Convert(varchar(10),getdate(),121) ='2009-1-4'
select * from A where Convert(varchar(10),getdate(),121) ='2009-1-4'
select * from A where Convert(…
[/Quote]

没看懂.

[So_CooL]

多謝樓上的提醒條件錯了，現在已修改，麻煩再幫我看看。謝謝

表A中有一個字段為BeginDt的字段值格式是：

BeginDt字段的值有可能是包含多個日期值,值與值之間用','分割的，格式如下

2009-1-4,2009-2-3,2009-3-7,
2009-4-4,2009-5-6,2009-7-12,

怎么樣跟將數據轉換成如下格式：

第一列
select * from A where Convert(varchar(10),getdate(),121) ='2009-1-4'
select * from A where Convert(varchar(10),getdate(),121) ='2009-2-3'
select * from A where Convert(varchar(10),getdate(),121) ='2009-3-7'

第二列
select * from A where Convert(varchar(10),getdate(),121) ='2009-4-4'
select * from A where Convert(varchar(10),getdate(),121) ='2009-5-6'
select * from A where Convert(varchar(10),getdate(),121) ='2009-7-12'
并將數據插入到一個臨時表中，

[liangCK]

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (data VARCHAR(100))
INSERT INTO @T
SELECT '2009-1-4,2009-2-3,2009-3-7' UNION ALL
SELECT '2009-4-4,2009-5-6,2009-7-12'

--SQL查询如下:

SELECT ID=IDENTITY(INT,1,1),* INTO #T FROM @T

SELECT TOP 100 ID=IDENTITY(INT,1,1) INTO #Seq FROM sysobjects

SELECT
    A.ID,
    SUBSTRING(A.data,B.ID,CHARINDEX(',',A.data+',',B.ID)-B.ID) AS s,
    RID=IDENTITY(INT,1,1)
INTO #data
FROM #T AS A
    JOIN #Seq AS B
        ON SUBSTRING(','+A.data,B.ID,1)=','

DECLARE @str VARCHAR(1000)
SET @str=''

SELECT @str=@str+',MAX(CASE WHEN ID='+RTRIM(ID)+' THEN s ELSE '''' END) dt'+RTRIM(ID)
FROM #T

SET @str=STUFF(@str,1,1,'')

EXEC ('
    SELECT '+@str+'
    FROM (
        SELECT 
            *,
            Seq=(SELECT COUNT(*)+1
                 FROM #data
                 WHERE ID=A.ID
                     AND RID<A.RID)
        FROM #data AS A
    ) AS A
    GROUP BY Seq
')

DROP TABLE #T,#Seq,#data

/*
dt1                                      dt2
-------------------------------- ------------------------
2009-1-4                               2009-4-4
2009-2-3                               2009-5-6
2009-3-7                               2009-7-12

(3 行受影响)

*/

[liangCK]

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (data VARCHAR(100))
INSERT INTO @T
SELECT '2009-1-4,2009-2-3,2009-3-7' UNION ALL
SELECT '2009-4-4,2009-5-6,2009-7-12' UNION ALL
SELECT '2009-2-4,2009-4-3,2009-7-7' 

--SQL查询如下:

SELECT ID=IDENTITY(INT,1,1),* INTO #T FROM @T

SELECT TOP 100 ID=IDENTITY(INT,1,1) INTO #Seq FROM sysobjects

SELECT
    A.ID,
    SUBSTRING(A.data,B.ID,CHARINDEX(',',A.data+',',B.ID)-B.ID) AS s,
    RID=IDENTITY(INT,1,1)
INTO #data
FROM #T AS A
    JOIN #Seq AS B
        ON SUBSTRING(','+A.data,B.ID,1)=','

DECLARE @str VARCHAR(1000)
SET @str=''

SELECT @str=@str+',MAX(CASE WHEN ID='+RTRIM(ID)+' THEN s ELSE '''' END) dt'+RTRIM(ID)
FROM #T

SET @str=STUFF(@str,1,1,'')

EXEC ('
    SELECT '+@str+'
    FROM (
        SELECT 
            *,
            Seq=(SELECT COUNT(*)+1
                 FROM #data
                 WHERE ID=A.ID
                     AND RID<A.RID)
        FROM #data AS A
    ) AS A
    GROUP BY Seq
')

DROP TABLE #T,#Seq,#data


/*
dt1                   dt2                     dt3
------------------- --------------------- ---------------
2009-1-4              2009-4-4               2009-2-4
2009-2-3              2009-5-6               2009-4-3
2009-3-7              2009-7-12              2009-7-7

(3 行受影响)
*/

[csdyyr]

CREATE TABLE TB(BeginDt VARCHAR(100), ID INT IDENTITY(1,1))
INSERT TB
SELECT '2009-1-4,2009-2-3,2009-3-7' UNION ALL 
SELECT '2009-4-4,2009-5-6,2009-7-12,2009-8-13' UNION ALL
SELECT '2009-1-1'

SELECT TOP 100 ID=IDENTITY(INT,1,1) INTO #T FROM SYSCOLUMNS

SELECT  A.ID,SUBSTRING(BeginDt, B.ID, CHARINDEX(',', BeginDt+',', B.ID)-B.ID) AS BeginDt,ID2=IDENTITY(INT,1,1) INTO TB2
FROM TB AS A , #T AS B
WHERE CHARINDEX(',', ','+BeginDt, B.ID)=B.ID

DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SELECT @SQL=@SQL+','+'MAX(CASE WHEN ID='+RTRIM(ID)+' THEN BeginDt ELSE NULL END) AS BeginDt'+RTRIM(ID)
FROM TB
GROUP BY ID

SET @SQL=STUFF(@SQL,1,1,'')

EXEC('SELECT '+@SQL+'
FROM (
SELECT *,SEQ=ID2-(SELECT COUNT(*) FROM TB2 WHERE ID<A.ID) FROM TB2 AS A
) T
GROUP BY SEQ
')

DROP TABLE #T
DROP TABLE TB2
DROP TABLE TB
/*
BeginDt1                                                                                             BeginDt2                                                                                             BeginDt3                                                                                             
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- 
2009-1-4                                                                                             2009-4-4                                                                                             2009-1-1
2009-2-3                                                                                             2009-5-6                                                                                             NULL
2009-3-7                                                                                             2009-7-12                                                                                            NULL
NULL                                                                                                 2009-8-13                                                                                            NULL
*/

[csdyyr]

CREATE TABLE TB(BeginDt VARCHAR(100), ID INT IDENTITY(1,1))
INSERT TB
SELECT '2009-1-4,2009-2-3,2009-3-7' UNION ALL 
SELECT '2009-4-4,2009-5-6,2009-7-12,2009-8-13' UNION ALL
SELECT '2009-1-1'

SELECT TOP 100 ID=IDENTITY(INT,1,1) INTO #T FROM SYSCOLUMNS

SELECT  A.ID,SUBSTRING(BeginDt, B.ID, CHARINDEX(',', BeginDt+',', B.ID)-B.ID) AS BeginDt,ID2=IDENTITY(INT,1,1) INTO TB2
FROM TB AS A , #T AS B
WHERE CHARINDEX(',', ','+BeginDt, B.ID)=B.ID

DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SELECT @SQL=@SQL+','+'MAX(CASE WHEN ID='+RTRIM(ID)+' THEN CONVERT(VARCHAR(10),BeginDt,120) ELSE NULL END) AS BeginDt'+RTRIM(ID)
FROM TB
GROUP BY ID

SET @SQL=STUFF(@SQL,1,1,'')

EXEC('SELECT '+@SQL+'
FROM (
SELECT *,SEQ=ID2-(SELECT COUNT(*) FROM TB2 WHERE ID<A.ID) FROM TB2 AS A
) T
GROUP BY SEQ
')

DROP TABLE #T
DROP TABLE TB2
DROP TABLE TB
/*
BeginDt1   BeginDt2   BeginDt3   
---------- ---------- ---------- 
2009-1-4   2009-4-4   2009-1-1
2009-2-3   2009-5-6   NULL
2009-3-7   2009-7-12  NULL
NULL       2009-8-13  NULL
*/

[liangCK]

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (data VARCHAR(100))
INSERT INTO @T
SELECT '2009-1-4,2009-2-3,2009-3-7' UNION ALL
SELECT '2009-4-4,2009-5-6,2009-7-12' UNION ALL
SELECT '2009-2-4,2009-4-3' 

--SQL查询如下:

SELECT ID=IDENTITY(INT,1,1),* INTO #T FROM @T

SELECT TOP 100 ID=IDENTITY(INT,1,1) INTO #Seq FROM sysobjects

SELECT
    A.ID,
    SUBSTRING(A.data,B.ID,CHARINDEX(',',A.data+',',B.ID)-B.ID) AS s,
    RID=IDENTITY(INT,1,1)
INTO #data
FROM #T AS A
    JOIN #Seq AS B
        ON SUBSTRING(','+A.data,B.ID,1)=','

SELECT
    data
FROM(
    SELECT data='第'+RTRIM(ID)+'列',ID,0 AS flag
    FROM #data
    GROUP BY ID
    UNION ALL
    SELECT 'SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='''+s+'''',ID,1
    FROM #data
) AS A
ORDER BY ID,flag

DROP TABLE #T,#Seq,#data

/*
data
-----------------------------------------------------
第1列
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-1-4'
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-2-3'
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-3-7'
第2列
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-4-4'
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-5-6'
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-7-12'
第3列
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-2-4'
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-4-3'

(11 行受影响)
*/

[liangCK]

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (data VARCHAR(100))
INSERT INTO @T
SELECT '2009-1-4,2009-2-3,2009-3-7' UNION ALL
SELECT '2009-4-4,2009-5-6,2009-7-12' UNION ALL
SELECT '2009-2-4,2009-4-3' 

--SQL查询如下:

SELECT ID=IDENTITY(INT,1,1),* INTO #T FROM @T

SELECT TOP 100 ID=IDENTITY(INT,1,1) INTO #Seq FROM sysobjects

SELECT
    A.ID,
    SUBSTRING(A.data,B.ID,CHARINDEX(',',A.data+',',B.ID)-B.ID) AS s,
    RID=IDENTITY(INT,1,1)
INTO #data
FROM #T AS A
    JOIN #Seq AS B
        ON SUBSTRING(','+A.data,B.ID,1)=','

SELECT
    data
INTO #插入到临时表中
FROM(
    SELECT data='第'+RTRIM(ID)+'列',ID,0 AS flag
    FROM #data
    GROUP BY ID
    UNION ALL
    SELECT 'SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='''+s+'''',ID,1
    FROM #data
) AS A
ORDER BY ID,flag

SELECT *
FROM #插入到临时表中

DROP TABLE #T,#Seq,#data,#插入到临时表中

/*
data
----------------------------------------------------
第1列
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-1-4'
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-2-3'
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-3-7'
第2列
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-4-4'
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-5-6'
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-7-12'
第3列
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-2-4'
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-4-3'

(11 行受影响)

*/

[csdyyr]

呵呵,原来这样啊

[liangCK]

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (data VARCHAR(100))
INSERT INTO @T
SELECT '2009-1-4,2009-2-3,2009-3-7' UNION ALL
SELECT '2009-4-4,2009-5-6,2009-7-12' UNION ALL
SELECT '2009-2-4,2009-4-3' 

--SQL查询如下:

--2005

;WITH Liang AS
(
    SELECT
        RID=ROW_NUMBER() OVER(ORDER BY GETDATE()),
        data
    FROM @T
),
Liang2 AS
(
    SELECT
        A.RID,
        data=B.x.value('.','varchar(10)')
    FROM (
        SELECT 
            RID,
            CONVERT(XML,'<v>'+REPLACE(data,',','</v><v>')+'</v>') AS data
        FROM Liang
    ) AS A
        CROSS APPLY A.data.nodes('//v') AS B(x)
)
SELECT
    data
INTO #临时表
FROM (
    SELECT 
        '第'+RTRIM(RID)+'列' AS data,
        RID,
        flag=0
    FROM Liang
    UNION ALL
    SELECT
        'SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='''+data+'''',
        RID,
        1
    FROM Liang2
) AS A
ORDER BY RID,flag


SELECT * FROM #临时表

DROP TABLE #临时表

/*
data
---------------------------------------------------------------------
第1列
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-1-4'
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-2-3'
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-3-7'
第2列
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-4-4'
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-5-6'
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-7-12'
第3列
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-2-4'
SELECT * FROM A WHERE CONVERT(VARCHAR(10),GETDATE(),121)='2009-4-3'

(11 行受影响)
*/

[liangCK]

12,13楼是SQL 2000的.
15楼是SQL 2005的.

自己参考..

[bluefangxiao]

学习...

[So_CooL]

Id NameName BeginDt
1 AA 2009-1-7,2009-8-8,2009-12-12,
2 BB 2009-2-2,2009-2-5,2009-12-12,
3 CC 2009-2-4,2009-1-7,2009-12-12,
結果：
1 AA 2009-1-7,2009-8-8,2009-12-12,
3 CC 2009-2-2,2009-1-7,2009-12-12,

查找日期BeginDt字段中的字符等於今天的日期值，存在則查詢出來（如2009-1-7是今天日期）
則有1和3查詢出來

這樣舉例看看有沒有問題。

[liangCK]

[Quote=引用 18 楼 So_CooL 的回复:]
Id NameName BeginDt
1 AA 2009-1-7,2009-8-8,2009-12-12,
2 BB 2009-2-2,2009-2-5,2009-12-12,
3 CC 2009-2-4,2009-1-7,2009-12-12,
結果：
1 AA 2009-1-7,2009-8-8,2009-12-12,
3 CC 2009-2-2,2009-1-7,2009-12-12,

查找日期BeginDt字段中的字符等於今天的日期值，存在則查詢出來（如2009-1-7是今天日期）
則有1和3查詢出來

這樣舉例看看有沒有問題。
[/Quote]

我汗..真无语了..这个查询..有你上面这样举例了吗?

[liangCK]

DECLARE @T TABLE (id INT,data VARCHAR(100))
INSERT INTO @T
SELECT 1,'2009-1-7,2009-2-3,2009-3-7' UNION ALL
SELECT 2,'2009-4-4,2009-5-6,2009-7-12' UNION ALL
SELECT 3,'2009-2-4,2009-1-7' 

SELECT *
FROM @T
WHERE CHARINDEX(RTRIM(YEAR(GETDATE()))+'-'+RTRIM(MONTH(GETDATE()))+'-'+RTRIM(DAY(GETDATE())),
              ','+data+',')>0
         

/*
id          data
----------- ----------------------------------------------------------------------------------------------------
1           2009-1-7,2009-2-3,2009-3-7
3           2009-2-4,2009-1-7

(2 行受影响)

*/

[liangCK]

DECLARE @T TABLE (id INT,data VARCHAR(100))
INSERT INTO @T
SELECT 1,'2009-1-7,2009-2-3,2009-3-7' UNION ALL
SELECT 2,'2009-4-4,2009-5-6,2009-7-12' UNION ALL
SELECT 3,'2009-2-4,2009-1-7' 

SELECT *
FROM @T
WHERE CHARINDEX(REPLACE(CONVERT(VARCHAR(10),GETDATE(),120),'-0','-'),','+data+',')>0


/*
id          data
----------- ----------------------------------------------------------------------------------------------------
1           2009-1-7,2009-2-3,2009-3-7
3           2009-2-4,2009-1-7

(2 行受影响)
*/

[So_CooL]

謝謝liangCK
結果是要這樣的。。