SQL 汇总高级查询 跪求大神....

[u010703853]

具体查询可描述如下图：

[sinat_28170733]

真难 回答不出来，谁回答谁是大神

[还在加载中灬]

--动态
DECLARE @SQL VARCHAR(MAX)
SET @SQL=''
SELECT @SQL=@SQL+',MAX(CASE WHEN 流程代码='''+T1.流程代码+''' THEN CONVERT(VARCHAR(30),时间,120)+''+++''+操作者 END)['+T2.名称+']'
FROM A表 T1 LEFT JOIN B表 T2 ON T1.流程代码=T2.流程代码 GROUP BY T1.流程代码,T2.名称
SET @SQL='SELECT '+STUFF(@SQL,1,1,'')+'FROM'
	+'(SELECT 单号,流程代码,时间,操作者,ROW_NUMBER()OVER(PARTITION BY 单号,流程代码 ORDER BY 时间)RN FROM A表)T'
	+' GROUP BY 单号,RN ORDER BY 单号'
--PRINT @SQL
EXEC(@SQL)

SQL2005+有效

[时间都去哪了啊]

gjgjk

引用 2 楼 ky_min 的回复:

--动态
DECLARE @SQL VARCHAR(MAX)
SET @SQL=''
SELECT @SQL=@SQL+',MAX(CASE WHEN 流程代码='''+T1.流程代码+''' THEN CONVERT(VARCHAR(30),时间,120)+''+++''+操作者 END)['+T2.名称+']'
FROM A表 T1 LEFT JOIN B表 T2 ON T1.流程代码=T2.流程代码 GROUP BY T1.流程代码,T2.名称
SET @SQL='SELECT '+STUFF(@SQL,1,1,'')+'FROM'
	+'(SELECT 单号,流程代码,时间,操作者,ROW_NUMBER()OVER(PARTITION BY 单号,流程代码 ORDER BY 时间)RN FROM A表)T'
	+' GROUP BY 单号,RN ORDER BY 单号'
--PRINT @SQL
EXEC(@SQL)

SQL2005+有效

试问，可以不用存储过程嘛？

[还在加载中灬]

套个外壳就可以了

CREATE PROCEDURE 存储过程名称
AS
BEGIN
DECLARE @SQL VARCHAR(MAX)
SET @SQL=''
SELECT @SQL=@SQL+',MAX(CASE WHEN 流程代码='''+T1.流程代码+''' THEN CONVERT(VARCHAR(30),时间,120)+''+++''+操作者 END)['+T2.名称+']'
FROM A表 T1 LEFT JOIN B表 T2 ON T1.流程代码=T2.流程代码 GROUP BY T1.流程代码,T2.名称
SET @SQL='SELECT '+STUFF(@SQL,1,1,'')+'FROM'
    +'(SELECT 单号,流程代码,时间,操作者,ROW_NUMBER()OVER(PARTITION BY 单号,流程代码 ORDER BY 时间)RN FROM A表)T'
    +' GROUP BY 单号,RN ORDER BY 单号'
--PRINT @SQL
EXEC(@SQL)
END
GO

[sinat_28170733]

SELECT num,convert(nvarchar(20),TIMES1,120)+'//'+Operat1 times1,convert(nvarchar(20),TIMES2,120)+'//'+Operat2 times2,
convert(nvarchar(20),TIMES3,120)+'//'+Operat3 times3,convert(nvarchar(20),TIMES4,120)+'//'+Operat4 times4,
convert(nvarchar(20),TIMES5,120)+'//'+Operat5 times5,convert(nvarchar(20),TIMES6,120)+'//'+Operat6 times6,
convert(nvarchar(20),TIMES7,120)+'//'+Operat7 times7,convert(nvarchar(20),TIMES8,120)+'//'+Operat8 times8,
convert(nvarchar(20),TIMES9,120)+'//'+Operat9 times9,convert(nvarchar(20),TIMES10,120)+'//'+Operat10 times10,
convert(nvarchar(20),TIMES11,120)+'//'+Operat11 times11,convert(nvarchar(20),TIMES12,120)+'//'+Operat12 times12 FROM
(
select num,
CASE WHEN location=11 THEN [times] END [times1],OPERAT OPERAT1,
CASE WHEN location=12 THEN [times] END [times2],OPERAT OPERAT2,
CASE WHEN location=13 THEN [times] END [times3],OPERAT OPERAT3,
CASE WHEN location=14 THEN [times] END [times4],OPERAT OPERAT4,
CASE WHEN location=15 THEN [times] END [times5],OPERAT OPERAT5,
CASE WHEN location=16 THEN [times] END [times6],OPERAT OPERAT6,
CASE WHEN location=17 THEN [times] END [times7],OPERAT OPERAT7,
CASE WHEN location=18 THEN [times] END [times8],OPERAT OPERAT8,
CASE WHEN location=19 THEN [times] END [times9],OPERAT OPERAT9,
CASE WHEN location=20 THEN [times] END [times10],OPERAT OPERAT10,
CASE WHEN location=21 THEN [times] END [times11],OPERAT OPERAT11,
CASE WHEN location=22 THEN [times] END [times12],OPERAT OPERAT12
from MDC_All 
)A
where num like '225544%'

[中国风]

DECLARE @Sql NVARCHAR(max),@Cols NVARCHAR(max),@Row INT
SET @Sql='',@Cols=''
SELECT TOP 1 @Row=COUNT(1) FROM A GROUP BY 单号,流程代码 ORDER BY COUNT(1) DESC\

SELECT @Cols=@Cols+','+QUOTENAME(名称)+'=max(CASE WHEN a.流程代码='+QUOTENAME(流程代码,'''')+')' FROM B

WHILE @Row>1
		SELECT  @Sql=@Sql+' UNION ALL SELECT 单号'+@Cols+',Row='+RTRIM(@Row)+' FROM (SELECT *,Row=ROW_NUMBER()OVER(PARTITION BY 单号,流程代码 ORDER BY 时间) FROM A)T WHERE Row='+RTRIM(@Row)+' GROUP BY 单号 ',@Row=@Row-1
SET @Sql='select * from ('+STUFF(@Sql,1,10,'')+')T  ORDER BY 单号,Row'
EXEC(@Sql)

	

[中国风]

按以下方法调试

DECLARE @Sql NVARCHAR(max),@Cols NVARCHAR(max),@Row INT
SELECT @Sql='',@Cols=''
SELECT TOP 1 @Row=COUNT(1) FROM A GROUP BY 单号,流程代码 ORDER BY COUNT(1) DESC
 
SELECT @Cols=@Cols+','+QUOTENAME(名称)+'=max(CASE WHEN a.流程代码='+QUOTENAME(流程代码,'''')+')' FROM B
 
WHILE @Row>1
        SELECT  @Sql=@Sql+' UNION ALL SELECT 单号'+@Cols+',Row='+RTRIM(@Row)+' FROM (SELECT *,Row=ROW_NUMBER()OVER(PARTITION BY 单号,流程代码 ORDER BY 时间) FROM A)T WHERE Row='+RTRIM(@Row)+' GROUP BY 单号 ',@Row=@Row-1
SET @Sql='select * from ('+STUFF(@Sql,1,10,'')+')T  ORDER BY 单号,Row'
EXEC(@Sql)