-
本版专家分：0结帖率 83.33% -
有形如下的数据
想通过sql 实现如下的展示效果
-
本版专家分：15
-
IF OBJECT_ID('tempdb..temp1') IS NOT NULL
BEGIN
DROP TABLE tempdb..temp1
end
;WITH t AS(SELECT '01001211001M1' AS wlbh,3400216 dwbh,1 hjsl,708.38 hjje UNION ALL
SELECT '01002101001M1' AS wlbh,1100040 dwbh,44 hjsl,110989.5 hjje UNION ALL
SELECT '01002101001M1' AS wlbh,3400216 dwbh,44 hjsl,1153292 hjje UNION ALL
SELECT '01002101001M1' AS wlbh,9900134 dwbh,0 hjsl,0 hjje UNION ALL
SELECT '01002211001M1' AS wlbh,3200105 dwbh,39 hjsl,39826.8 hjje UNION ALL
SELECT '01002211001M1' AS wlbh,3400216 dwbh,17 hjsl,19416.19 hjje UNION ALL
SELECT '01003101001M1' AS wlbh,3400216 dwbh,32 hjsl,105006.9 hjje UNION ALL
SELECT '01003101001M1' AS wlbh,4100033 dwbh,86 hjsl,354978 hjje UNION ALL
SELECT '01003101001M1' AS wlbh,9900134 dwbh,0 hjsl,0 hjje UNION ALL
SELECT '01003211001M1' AS wlbh,2100105 dwbh,72 hjsl,98568 hjje UNION ALL
SELECT '01003211001M1' AS wlbh,3400216 dwbh,16 hjsl,24793.61 hjje)
SELECT *,ROW_NUMBER()OVER(PARTITION BY wlbh ORDER BY dwbh ASC) AS rnk INTO tempdb..temp1 FROM t
DECLARE @loopcnt AS INT
SELECT @loopcnt=MAX(rnk) FROM tempdb..temp1
DECLARE @sql NVARCHAR(MAX)
DECLARE @i AS INT
SET @sql=''
SET @i=1
WHILE (@i<=@loopcnt)
BEGIN
SET @sql=@sql+
',dwbh'+CONVERT(NVARCHAR(MAX),@i)+'=isnull((select convert(nvarchar(max),dwbh) from tempdb..temp1 b where a.wlbh=b.wlbh and rnk='+CONVERT(NVARCHAR(MAX),@i)+'),'''')
,hjsl'+CONVERT(NVARCHAR(MAX),@i)+'=isnull((select convert(nvarchar(max),hjsl) from tempdb..temp1 b where a.wlbh=b.wlbh and rnk='+CONVERT(NVARCHAR(MAX),@i)+'),'''')
,hjje'+CONVERT(NVARCHAR(MAX),@i)+'=isnull((select convert(nvarchar(max),hjje) from tempdb..temp1 b where a.wlbh=b.wlbh and rnk='+CONVERT(NVARCHAR(MAX),@i)+'),'''')
'
SET @i=@i+1
end
SET @sql='select
wlbh'+@sql+
' from tempdb..temp1 a
group by wlbh'
EXEC (@sql)
IF OBJECT_ID('tempdb..temp1') IS NOT NULL
BEGIN
DROP TABLE tempdb..temp1
end
-
本版专家分：10668
-
- 蓝花 2018年9月 MS-SQL Server大版内专家分月排行榜第三
-
-
借用#1的数据
IF OBJECT_ID(N'TEMPDB..#A') IS NOT NULL
DROP TABLE #A
GO
CREATE TABLE #A
(WLBH VARCHAR(20),
DWBH VARCHAR(10),
HJSL INT,
HJJE DECIMAL(18,4))
INSERT INTO #A
SELECT '01001211001M1' AS wlbh,'3400216' dwbh,1 hjsl,708.38 hjje UNION ALL
SELECT '01002101001M1' AS wlbh,'1100040' dwbh,44 hjsl,110989.5 hjje UNION ALL
SELECT '01002101001M1' AS wlbh,'3400216' dwbh,44 hjsl,1153292 hjje UNION ALL
SELECT '01002101001M1' AS wlbh,'9900134' dwbh,0 hjsl,0 hjje UNION ALL
SELECT '01002211001M1' AS wlbh,'3200105' dwbh,39 hjsl,39826.8 hjje UNION ALL
SELECT '01002211001M1' AS wlbh,'3400216' dwbh,17 hjsl,19416.19 hjje UNION ALL
SELECT '01003101001M1' AS wlbh,'3400216' dwbh,32 hjsl,105006.9 hjje UNION ALL
SELECT '01003101001M1' AS wlbh,'4100033' dwbh,86 hjsl,354978 hjje UNION ALL
SELECT '01003101001M1' AS wlbh,'9900134' dwbh,0 hjsl,0 hjje UNION ALL
SELECT '01003211001M1' AS wlbh,'2100105' dwbh,72 hjsl,98568 hjje UNION ALL
SELECT '01003211001M1' AS wlbh,'3400216' dwbh,16 hjsl,24793.61 hjje
DECLARE @SQL VARCHAR(MAX)
WITH CTE
AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY WLBH ORDER BY (SELECT 1)) AS SEQ FROM #A)
SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN SEQ='+CAST(SEQ AS VARCHAR)+' THEN DWBH ELSE '''' END) AS DWBH'+CAST(SEQ AS VARCHAR)+
',MAX(CASE WHEN SEQ='+CAST(SEQ AS VARCHAR)+' THEN CAST(HJSL AS VARCHAR) ELSE '''' END) AS HJSL'+CAST(SEQ AS VARCHAR)+
',MAX(CASE WHEN SEQ='+CAST(SEQ AS VARCHAR)+' THEN CAST(HJJE AS VARCHAR) ELSE '''' END) AS HJJE'+CAST(SEQ AS VARCHAR)+''
FROM (SELECT SEQ FROM CTE GROUP BY SEQ) AS A
SET @SQL='WITH CTE
AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY WLBH ORDER BY (SELECT 1)) AS SEQ FROM #A)
SELECT WLBH,'+ @SQL +' FROM CTE GROUP BY WLBH'
EXEC(@SQL)
