SQL 二维表

[oHaiBin123456]

有形如下的数据



想通过sql 实现如下的展示效果

[RINK_1]

借用#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) 

[weixin_42334999]

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