双11狂欢大促

盲盒赢 定制羽绒服

开启惊喜盲盒，抽取你的专属好礼

— 惊喜奖品抢先看 —

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

SQL 二维表

2019-01-30 11:14:49 432 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

oHaiBin123456 墨衍会员 · AI 创作全网分发关注 + 加入社区

CentOS7下Ollama部署全攻略：从安装到自定义端口配置（附国内加速技巧）本文详细介绍了在CentOS7系统上部署Ollama的完整流程，包括环境准备、模型下载优化、服务化部署及端口配置等关键步骤。特别针对国内网络环境提供了加速安装方案和镜像源配置技巧，帮助开发者快速搭建稳定的本地AI运行环境。阅读详情

有形如下的数据

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

...全文

别再怕无霍尔BLDC了！手把手教你用反电动势过零检测搞定电机换向（附STM32代码片段）本文详细介绍了无霍尔传感器BLDC电机的驱动方法，重点讲解反电动势过零检测技术及其在STM32上的实现。通过硬件电路设计、信号调理和软件算法优化，手把手教你完成电机换向控制，并提供实用的调试技巧和代码片段，帮助开发者快速掌握无霍尔BLDC驱动技术。阅读详情

oHaiBin123456

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

weixin_42334999 2019-01-30

打赏
举报


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

RINK_1 2019-01-30

打赏
举报

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

✦双11狂欢大促✦

SQL 二维表

相关推荐

双11狂欢大促