22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE VIEW VIEW_190521
AS
WITH CTE
AS
(SELECT PRODUCT_ID,CAST(STEP_ID AS VARCHAR) AS STEP_ID ,'工序名' AS NAME,STEP_NAME AS STEP_VALUE,1 AS SORT_ID FROM #T
UNION ALL
SELECT PRODUCT_ID,CAST(STEP_ID AS VARCHAR) AS STEP_ID,'本厂定额' AS NAME,CAST(SELF_QTY AS VARCHAR),2 FROM #T
UNION ALL
SELECT PRODUCT_ID,'合计','本厂定额' AS NAME,CAST(SUM(SELF_QTY) AS VARCHAR),2 FROM #T GROUP BY PRODUCT_ID
UNION ALL
SELECT PRODUCT_ID,CAST(STEP_ID AS VARCHAR) AS STEP_ID,'外协定额' AS NAME,CAST(VENDOR_QTY AS VARCHAR),3 FROM #T
UNION ALL
SELECT PRODUCT_ID,'合计','外协定额' AS NAME,CAST(SUM(VENDOR_QTY) AS VARCHAR),3 FROM #T GROUP BY PRODUCT_ID)
SELECT *
FROM CTE A
PIVOT
(MAX(STEP_VALUE) FOR STEP_ID IN ([5],[10],[15],[20],[25],[30],[35],[40],[45],[50],[55],[60],[65],[70],[75],[80],[合计])) AS B
/*查询视图*/
SELECT * FROM VIEW_190521
ORDER BY PRODUCT_ID,SORT_ID
[/quote]
请问老师,能不能在列名5,10,15 ........这前面加上工序两个字,即显示工序5,工序10 .......[/quote]
ALTER VIEW [dbo].[VIEW_190521]
AS
WITH CTE
AS
(SELECT PRODUCT_ID,'工序'+CAST(STEP_ID AS VARCHAR) AS STEP_ID ,'工序名' AS NAME,STEP_NAME AS STEP_VALUE,1 AS SORT_ID FROM #T
UNION ALL
SELECT PRODUCT_ID,'工序'+CAST(STEP_ID AS VARCHAR) AS STEP_ID,'本厂定额' AS NAME,CAST(SELF_QTY AS VARCHAR),2 FROM #T
UNION ALL
SELECT PRODUCT_ID,'合计','本厂定额' AS NAME,CAST(SUM(SELF_QTY) AS VARCHAR),2 FROM #T GROUP BY PRODUCT_ID
UNION ALL
SELECT PRODUCT_ID,'工序'+CAST(STEP_ID AS VARCHAR) AS STEP_ID,'外协定额' AS NAME,CAST(VENDOR_QTY AS VARCHAR),3 FROM #T
UNION ALL
SELECT PRODUCT_ID,'合计','外协定额' AS NAME,CAST(SUM(VENDOR_QTY) AS VARCHAR),3 FROM #T GROUP BY PRODUCT_ID)
SELECT *
FROM CTE A
PIVOT
(MAX(STEP_VALUE) FOR STEP_ID IN ([工序5],[工序10],[工序15],[工序20],[工序25],[工序30],[工序35],[工序40],[工序45],[工序50],[工序55],[工序60],[工序65],[工序70],[工序75],[工序80],[合计])) AS B
GO
[/quote]
感谢老师,这个问题终于搞定了。
CREATE VIEW VIEW_190521
AS
WITH CTE
AS
(SELECT PRODUCT_ID,CAST(STEP_ID AS VARCHAR) AS STEP_ID ,'工序名' AS NAME,STEP_NAME AS STEP_VALUE,1 AS SORT_ID FROM #T
UNION ALL
SELECT PRODUCT_ID,CAST(STEP_ID AS VARCHAR) AS STEP_ID,'本厂定额' AS NAME,CAST(SELF_QTY AS VARCHAR),2 FROM #T
UNION ALL
SELECT PRODUCT_ID,'合计','本厂定额' AS NAME,CAST(SUM(SELF_QTY) AS VARCHAR),2 FROM #T GROUP BY PRODUCT_ID
UNION ALL
SELECT PRODUCT_ID,CAST(STEP_ID AS VARCHAR) AS STEP_ID,'外协定额' AS NAME,CAST(VENDOR_QTY AS VARCHAR),3 FROM #T
UNION ALL
SELECT PRODUCT_ID,'合计','外协定额' AS NAME,CAST(SUM(VENDOR_QTY) AS VARCHAR),3 FROM #T GROUP BY PRODUCT_ID)
SELECT *
FROM CTE A
PIVOT
(MAX(STEP_VALUE) FOR STEP_ID IN ([5],[10],[15],[20],[25],[30],[35],[40],[45],[50],[55],[60],[65],[70],[75],[80],[合计])) AS B
/*查询视图*/
SELECT * FROM VIEW_190521
ORDER BY PRODUCT_ID,SORT_ID
[/quote]
请问老师,能不能在列名5,10,15 ........这前面加上工序两个字,即显示工序5,工序10 .......[/quote]
ALTER VIEW [dbo].[VIEW_190521]
AS
WITH CTE
AS
(SELECT PRODUCT_ID,'工序'+CAST(STEP_ID AS VARCHAR) AS STEP_ID ,'工序名' AS NAME,STEP_NAME AS STEP_VALUE,1 AS SORT_ID FROM #T
UNION ALL
SELECT PRODUCT_ID,'工序'+CAST(STEP_ID AS VARCHAR) AS STEP_ID,'本厂定额' AS NAME,CAST(SELF_QTY AS VARCHAR),2 FROM #T
UNION ALL
SELECT PRODUCT_ID,'合计','本厂定额' AS NAME,CAST(SUM(SELF_QTY) AS VARCHAR),2 FROM #T GROUP BY PRODUCT_ID
UNION ALL
SELECT PRODUCT_ID,'工序'+CAST(STEP_ID AS VARCHAR) AS STEP_ID,'外协定额' AS NAME,CAST(VENDOR_QTY AS VARCHAR),3 FROM #T
UNION ALL
SELECT PRODUCT_ID,'合计','外协定额' AS NAME,CAST(SUM(VENDOR_QTY) AS VARCHAR),3 FROM #T GROUP BY PRODUCT_ID)
SELECT *
FROM CTE A
PIVOT
(MAX(STEP_VALUE) FOR STEP_ID IN ([工序5],[工序10],[工序15],[工序20],[工序25],[工序30],[工序35],[工序40],[工序45],[工序50],[工序55],[工序60],[工序65],[工序70],[工序75],[工序80],[合计])) AS B
GO
CREATE VIEW VIEW_190521
AS
WITH CTE
AS
(SELECT PRODUCT_ID,CAST(STEP_ID AS VARCHAR) AS STEP_ID ,'工序名' AS NAME,STEP_NAME AS STEP_VALUE,1 AS SORT_ID FROM #T
UNION ALL
SELECT PRODUCT_ID,CAST(STEP_ID AS VARCHAR) AS STEP_ID,'本厂定额' AS NAME,CAST(SELF_QTY AS VARCHAR),2 FROM #T
UNION ALL
SELECT PRODUCT_ID,'合计','本厂定额' AS NAME,CAST(SUM(SELF_QTY) AS VARCHAR),2 FROM #T GROUP BY PRODUCT_ID
UNION ALL
SELECT PRODUCT_ID,CAST(STEP_ID AS VARCHAR) AS STEP_ID,'外协定额' AS NAME,CAST(VENDOR_QTY AS VARCHAR),3 FROM #T
UNION ALL
SELECT PRODUCT_ID,'合计','外协定额' AS NAME,CAST(SUM(VENDOR_QTY) AS VARCHAR),3 FROM #T GROUP BY PRODUCT_ID)
SELECT *
FROM CTE A
PIVOT
(MAX(STEP_VALUE) FOR STEP_ID IN ([5],[10],[15],[20],[25],[30],[35],[40],[45],[50],[55],[60],[65],[70],[75],[80],[合计])) AS B
/*查询视图*/
SELECT * FROM VIEW_190521
ORDER BY PRODUCT_ID,SORT_ID
[/quote]
请问老师,能不能在列名5,10,15 ........这前面加上工序两个字,即显示工序5,工序10 .......
CREATE VIEW VIEW_190521
AS
WITH CTE
AS
(SELECT PRODUCT_ID,CAST(STEP_ID AS VARCHAR) AS STEP_ID ,'工序名' AS NAME,STEP_NAME AS STEP_VALUE,1 AS SORT_ID FROM #T
UNION ALL
SELECT PRODUCT_ID,CAST(STEP_ID AS VARCHAR) AS STEP_ID,'本厂定额' AS NAME,CAST(SELF_QTY AS VARCHAR),2 FROM #T
UNION ALL
SELECT PRODUCT_ID,'合计','本厂定额' AS NAME,CAST(SUM(SELF_QTY) AS VARCHAR),2 FROM #T GROUP BY PRODUCT_ID
UNION ALL
SELECT PRODUCT_ID,CAST(STEP_ID AS VARCHAR) AS STEP_ID,'外协定额' AS NAME,CAST(VENDOR_QTY AS VARCHAR),3 FROM #T
UNION ALL
SELECT PRODUCT_ID,'合计','外协定额' AS NAME,CAST(SUM(VENDOR_QTY) AS VARCHAR),3 FROM #T GROUP BY PRODUCT_ID)
SELECT *
FROM CTE A
PIVOT
(MAX(STEP_VALUE) FOR STEP_ID IN ([5],[10],[15],[20],[25],[30],[35],[40],[45],[50],[55],[60],[65],[70],[75],[80],[合计])) AS B
/*查询视图*/
SELECT * FROM VIEW_190521
ORDER BY PRODUCT_ID,SORT_ID
IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T
(PRODUCT_ID NVARCHAR(20),
STEP_ID INT,
STEP_NAME NVARCHAR(20),
SELF_QTY INT,
VENDOR_QTY INT)
INSERT INTO #T
SELECT 'LBJ000468','5','下料',1,NULL UNION ALL
SELECT 'LBJ000468','10','粗车',NULL,2 UNION ALL
SELECT 'LBJ000468','15','热处理',3,NULL UNION ALL
SELECT 'LBJ000468','20','精车',NULL,6 UNION ALL
SELECT 'LBJ000468','25','入库',NULL,NULL UNION ALL
SELECT 'LBJ000469','5','下料',1,NULL UNION ALL
SELECT 'LBJ000469','10','粗车',NULL,4 UNION ALL
SELECT 'LBJ000469','15','热处理',3,NULL UNION ALL
SELECT 'LBJ000469','20','精车',NULL,8 UNION ALL
SELECT 'LBJ000469','25','油漆',1,NULL UNION ALL
SELECT 'LBJ000469','30','入库',NULL,NULL
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL=ISNULL(@SQL+',','')+'
MAX(CASE WHEN STEP_ID='''+STEP_ID+''' THEN STEP_VALUE ELSE '''' END) AS '''+STEP_ID+''''
FROM (SELECT CAST(STEP_ID AS VARCHAR) AS STEP_ID FROM #T GROUP BY STEP_ID
UNION ALL
SELECT '合计') AS A
SET @SQL='WITH CTE
AS
(SELECT PRODUCT_ID,CAST(STEP_ID AS VARCHAR) AS STEP_ID ,''工序名'' AS NAME,STEP_NAME AS STEP_VALUE,1 AS SORT_ID FROM #T
UNION ALL
SELECT PRODUCT_ID,CAST(STEP_ID AS VARCHAR) AS STEP_ID,''本厂定额'' AS NAME,CAST(SELF_QTY AS VARCHAR),2 FROM #T
UNION ALL
SELECT PRODUCT_ID,''合计'',''本厂定额'' AS NAME,CAST(SUM(SELF_QTY) AS VARCHAR),2 FROM #T GROUP BY PRODUCT_ID
UNION ALL
SELECT PRODUCT_ID,CAST(STEP_ID AS VARCHAR) AS STEP_ID,''外协定额'' AS NAME,CAST(VENDOR_QTY AS VARCHAR),3 FROM #T
UNION ALL
SELECT PRODUCT_ID,''合计'',''外协定额'' AS NAME,CAST(SUM(VENDOR_QTY) AS VARCHAR),3 FROM #T GROUP BY PRODUCT_ID)
SELECT PRODUCT_ID,NAME,SORT_ID,'+@SQL+' FROM CTE
GROUP BY PRODUCT_ID,NAME,SORT_ID
ORDER BY PRODUCT_ID,SORT_ID'
EXEC(@SQL)