小白问个问题

qq_31871069 2017-02-12 10:08:08

如何通过sql语句来完成。谢谢

...全文

166 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

二月十六 2017-02-16

打赏
举报

引用 7 楼 qq_31871069 的回复:

您好，那个#tt每次用的时候怎么可以重新生成呢。是个小白。

这个也是个临时表应该是你的数据表

qq_31871069 2017-02-15

打赏
举报

引用 6 楼 sinat_28984567 的回复:

[quote=引用 5 楼 qq_31871069 的回复:] ;WITH tab1(产线,日期,规格)AS( SELECT 'BL01','2017-01-02','A1' UNION ALL SELECT 'BL01','2017-01-06','A2' UNION ALL SELECT 'BL01','2017-01-12','A1' UNION ALL SELECT 'BL01','2017-01-20','A1' UNION ALL SELECT 'BL01','2017-01-25','A5' UNION ALL 这段话能不能动态呢

这个是测试数据，这个tab1应该是你真正的数据表，下边是读取的语句。 [/quote] 您好，那个#tt每次用的时候怎么可以重新生成呢。是个小白。

二月十六 2017-02-15

打赏
举报

引用 5 楼 qq_31871069 的回复:

;WITH tab1(产线,日期,规格)AS( SELECT 'BL01','2017-01-02','A1' UNION ALL SELECT 'BL01','2017-01-06','A2' UNION ALL SELECT 'BL01','2017-01-12','A1' UNION ALL SELECT 'BL01','2017-01-20','A1' UNION ALL SELECT 'BL01','2017-01-25','A5' UNION ALL 这段话能不能动态呢

这个是测试数据，这个tab1应该是你真正的数据表，下边是读取的语句。

qq_31871069 2017-02-15

打赏
举报

;WITH tab1(产线,日期,规格)AS( SELECT 'BL01','2017-01-02','A1' UNION ALL SELECT 'BL01','2017-01-06','A2' UNION ALL SELECT 'BL01','2017-01-12','A1' UNION ALL SELECT 'BL01','2017-01-20','A1' UNION ALL SELECT 'BL01','2017-01-25','A5' UNION ALL 这段话能不能动态呢

引用 3 楼 sinat_28984567 的回复:

语句：

---测试数据
;WITH tab1(产线,日期,规格)AS(
SELECT 'BL01','2017-01-02','A1' UNION ALL
SELECT 'BL01','2017-01-06','A2' UNION ALL
SELECT 'BL01','2017-01-12','A1' UNION ALL
SELECT 'BL01','2017-01-20','A1' UNION ALL
SELECT 'BL01','2017-01-25','A5' UNION ALL
SELECT 'BL02','2017-01-03','A3' UNION ALL
SELECT 'BL02','2017-01-08','A4' UNION ALL
SELECT 'BL02','2017-01-13','A3' UNION ALL
SELECT 'BL02','2017-01-19','A4' UNION ALL
SELECT 'BL02','2017-01-26','A1' UNION ALL
SELECT 'BL03','2017-01-03','A5' UNION ALL
SELECT 'BL03','2017-01-07','A2' UNION ALL
SELECT 'BL03','2017-01-14','A1' UNION ALL
SELECT 'BL03','2017-01-21','A2' UNION ALL
SELECT 'BL03','2017-01-27','A1'
)
---测试数据结束
SELECT  日期 ,
        MAX(CASE WHEN tab1.产线 = 'BL01' THEN tab1.规格
                 ELSE ''
            END) AS 'BL01' ,
        MAX(CASE WHEN tab1.产线 = 'BL02' THEN tab1.规格
                 ELSE ''
            END) AS 'BL02' ,
        MAX(CASE WHEN tab1.产线 = 'BL03' THEN tab1.规格
                 ELSE ''
            END) AS 'BL03'
FROM    tab1
GROUP BY tab1.日期

结果：

不知道楼主要啥，不知道表2是怎么来的，是原来就有还是通过表一生成。不过有了这个结果之后怎么处理都应该简单了吧。

qq_31871069 2017-02-15

打赏
举报

谢谢才来结贴。就是想按照tab1转换成tab2的格式方便查询

道素 2017-02-12

打赏
举报



CREATE TABLE #tt(Line VARCHAR(10),dt DATETIME,model VARCHAR(10))
INSERT INTO #tt
SELECT 'BL01',CONVERT(DATETIME,'2017-01-02'),'A1' UNION ALL
SELECT 'BL01','2017-01-06','A2' UNION ALL
SELECT 'BL01','2017-01-12','A1' UNION ALL
SELECT 'BL01','2017-01-20','A1' UNION ALL
SELECT 'BL01','2017-01-25','A5' UNION ALL
SELECT 'BL02','2017-01-03','A3' UNION ALL
SELECT 'BL02','2017-01-08','A4' UNION ALL
SELECT 'BL02','2017-01-13','A3' UNION ALL
SELECT 'BL02','2017-01-19','A4' UNION ALL
SELECT 'BL02','2017-01-26','A1' UNION ALL
SELECT 'BL03','2017-01-03','A5' UNION ALL
SELECT 'BL03','2017-01-07','A2' UNION ALL
SELECT 'BL03','2017-01-14','A1' UNION ALL
SELECT 'BL03','2017-01-21','A2' UNION ALL
SELECT 'BL03','2017-01-27','A1'   

DECLARE @StartDate DATETIME='2016-12-29',@EndDate DATETIME='2017-02-02'
DECLARE @col1 VARCHAR(max),@col2 VARCHAR(max),@sql VARCHAR(max)
SELECT @col1=ISNULL(@col1+',','')+QUOTENAME(line),@col2=ISNULL(@col2,'')+',ISNULL('+QUOTENAME(Line)+','''') AS '+QUOTENAME(line) FROM #tt GROUP BY line
PRINT @col1
SET @sql='
SELECT dt'+@col2+' FROM (
    SELECT DATEADD(d,sv.number,'''+CONVERT(VARCHAR,@StartDate,110)+''') AS dt,s.model AS model,s.Line  FROM master.dbo.spt_values AS sv 
    LEFT JOIN #tt AS s ON DATEDIFF(d,DATEADD(d,sv.number,'''+CONVERT(VARCHAR,@StartDate,110)+'''),s.dt)=0
    WHERE sv.type=''P'' AND sv.number BETWEEN 0 AND DATEDIFF(d,'''+CONVERT(VARCHAR,@StartDate,110)+''','''+CONVERT(VARCHAR,@EndDate,110)+''')-1
) AS t PIVOT(MAX(model) FOR line in ('+@col1+')) p'
PRINT @sql
EXEC(@sql)


dt                      BL01       BL02       BL03
----------------------- ---------- ---------- ----------
2016-12-29 00:00:00.000                       
2016-12-30 00:00:00.000                       
2016-12-31 00:00:00.000                       
2017-01-01 00:00:00.000                       
2017-01-02 00:00:00.000 A1                    
2017-01-03 00:00:00.000            A3         A5
2017-01-04 00:00:00.000                       
2017-01-05 00:00:00.000                       
2017-01-06 00:00:00.000 A2                    
2017-01-07 00:00:00.000                       A2
2017-01-08 00:00:00.000            A4         
2017-01-09 00:00:00.000                       
2017-01-10 00:00:00.000                       
2017-01-11 00:00:00.000                       
2017-01-12 00:00:00.000 A1                    
2017-01-13 00:00:00.000            A3         
2017-01-14 00:00:00.000                       A1
2017-01-15 00:00:00.000                       
2017-01-16 00:00:00.000                       
2017-01-17 00:00:00.000                       
2017-01-18 00:00:00.000                       
2017-01-19 00:00:00.000            A4         
2017-01-20 00:00:00.000 A1                    
2017-01-21 00:00:00.000                       A2
2017-01-22 00:00:00.000                       
2017-01-23 00:00:00.000                       
2017-01-24 00:00:00.000                       
2017-01-25 00:00:00.000 A5                    
2017-01-26 00:00:00.000            A1         
2017-01-27 00:00:00.000                       A1
2017-01-28 00:00:00.000                       
2017-01-29 00:00:00.000                       
2017-01-30 00:00:00.000                       
2017-01-31 00:00:00.000                       
2017-02-01 00:00:00.000

二月十六 2017-02-12

打赏
举报

语句：

---测试数据

;WITH tab1(产线,日期,规格)AS(

SELECT 'BL01','2017-01-02','A1' UNION ALL

SELECT 'BL01','2017-01-06','A2' UNION ALL

SELECT 'BL01','2017-01-12','A1' UNION ALL

SELECT 'BL01','2017-01-20','A1' UNION ALL

SELECT 'BL01','2017-01-25','A5' UNION ALL

SELECT 'BL02','2017-01-03','A3' UNION ALL

SELECT 'BL02','2017-01-08','A4' UNION ALL

SELECT 'BL02','2017-01-13','A3' UNION ALL

SELECT 'BL02','2017-01-19','A4' UNION ALL

SELECT 'BL02','2017-01-26','A1' UNION ALL

SELECT 'BL03','2017-01-03','A5' UNION ALL

SELECT 'BL03','2017-01-07','A2' UNION ALL

SELECT 'BL03','2017-01-14','A1' UNION ALL

SELECT 'BL03','2017-01-21','A2' UNION ALL

SELECT 'BL03','2017-01-27','A1'

)

---测试数据结束

SELECT  日期 ,

        MAX(CASE WHEN tab1.产线 = 'BL01' THEN tab1.规格

                 ELSE ''

            END) AS 'BL01' ,

        MAX(CASE WHEN tab1.产线 = 'BL02' THEN tab1.规格

                 ELSE ''

            END) AS 'BL02' ,

        MAX(CASE WHEN tab1.产线 = 'BL03' THEN tab1.规格

                 ELSE ''

            END) AS 'BL03'

FROM    tab1

GROUP BY tab1.日期