SQL动态将多行转换成单行显示

脚跟着地 2017-07-01 08:03:34
现有数据如下：


将此数据转换成


最大行数不确定，需要动态生成列名torque1、troque2、torque3.。。。。。以最大相同的barcode and torqueName相同为准 ，后面动态生成列名angle1,angle2,angle3.......与torque列数相同

我现在的查询语句为：
select top 100 P.torque as torqueName,PARAM.BOLTTYPE,opno,barcode,ICS.type,ICS.boltno,ICS.confno,ICS.torque,angle FROM ICSDATA ICS JOIN PROGRAMTORQUE P ON ICS.CONFNO=P.CONFNO JOIN PARAM ON P.TORQUE=PARAM.TORQUE WHERE ICS.ID NOT IN(select top 0 id from icsdata order by id desc) order by ICS.id desc;


希望能将这个语句整合进去，求大神帮忙，谢谢！

逗点分隔文件如下，便于您导入做测试，谢谢了
5/(20-30),M16*3,OP10,111,111,3,5,2.60,1.00

5/(20-30),M16*3,OP10,111,111,3,5,2.10,1.00

5/(20-30),M16*3,OP10,111,111,3,5,2.00,1.00

2,M16*2,OP10,111,111,3,3,2.50,1.00

2,M16*2,OP10,111,111,3,3,2.40,1.00

2,M16*2,OP10,111,111,3,3,2.20,1.00

1.5,M16*1,OP10,111,111,2,2,2.40,1.00

1.5,M16*1,OP10,111,111,2,2,2.20,1.00

5/(20-30),M16*3,OP10,112,111,3,5,2.30,1.00

5/(20-30),M16*3,OP10,112,111,3,5,2.30,1.00

5/(20-30),M16*3,OP10,112,111,3,5,2.30,1.00

2,M16*2,OP10,112,111,3,3,2.30,1.00

2,M16*2,OP10,112,111,3,3,2.30,1.00

2,M16*2,OP10,112,111,3,3,2.30,1.00

1.5,M16*1,OP10,112,111,2,2,2.30,1.00

1.5,M16*1,OP10,112,111,2,2,2.30,1.00
...全文
437 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
RICHEER COCA 2017-07-05
  • 打赏
  • 举报
 回复
引用 5 楼 ch21st 的回复:
更正下SQL，忘了将动态列换成变量了


DECLARE @cols VARCHAR(max),@sql VARCHAR(max)

SELECT @cols=ISNULL(@cols+',','')+'torque'+LTRIM(sv.number)+',angle'+LTRIM(sv.number) FROM master.dbo.spt_values AS sv 

CROSS APPLY(SELECT  TOP 1 COUNT(0) AS cnt FROM #tab AS t GROUP BY torqueName,barcode ORDER BY COUNT(0) DESC) c

WHERE  sv.type='P' AND sv.number BETWEEN 1 AND c.cnt

PRINT @cols

SET @sql='

SELECT * FROM  (

        SELECT   t.torqueName,t.BOLTTYPE,t.opno,t.barcode,t.type,t.boltno,t.confno,c.tile,c.val

        FROM (

            SELECT *,ROW_NUMBER()OVER(PARTITION BY torqueName,barcode,opno,type,boltno,confno ORDER BY GETDATE()) AS rn 

            FROM #tab

        ) AS t CROSS APPLY(VALUES(''torque''+LTRIM(rn),t.torque),(''angle''+LTRIM(rn),t.angle))c(tile,val)

) AS tt

PIVOT(MAX(val) FOR tile IN ('+@cols+'))p

ORDER BY p.barcode,p.torqueName DESC'

EXEC(@sql)





  +------------+----------+------+---------+------+--------+--------+---------+--------+---------+--------+---------+--------+

| torqueName | BOLTTYPE | opno | barcode | type | boltno | confno | torque1 | angle1 | torque2 | angle2 | torque3 | angle3 |

+------------+----------+------+---------+------+--------+--------+---------+--------+---------+--------+---------+--------+

| 5/(20-30)  | M16*3    | OP10 | 111     | 111  | 3      | 5      | 2.6     | 1      | 2.1     | 1      | 2       | 1      |

| 2          | M16*2    | OP10 | 111     | 111  | 3      | 3      | 2.5     | 1      | 2.4     | 1      | 2.2     | 1      |

| 1.5        | M16*1    | OP10 | 111     | 111  | 2      | 2      | 2.4     | 1      | 2.2     | 1      | NULL    | NULL   |

| 5/(20-30)  | M16*3    | OP10 | 112     | 111  | 3      | 5      | 2.3     | 1      | 2.3     | 1      | 2.3     | 1      |

| 2          | M16*2    | OP10 | 112     | 111  | 3      | 3      | 2.3     | 1      | 2.3     | 1      | 2.3     | 1      |

| 1.5        | M16*1    | OP10 | 112     | 111  | 2      | 2      | 2.3     | 1      | 2.3     | 1      | NULL    | NULL   |

+------------+----------+------+---------+------+--------+--------+---------+--------+---------+--------+---------+--------+


强大的SQL
江南雪_158 2017-07-03
  • 打赏
  • 举报
 回复
引用 6 楼 zjz444 的回复:
DECLARE @cols VARCHAR(max),@sql VARCHAR(max)
with #tab as (select top 100 P.torque as torqueName,PARAM.BOLTTYPE,opno,barcode,ICS.type,ICS.boltno,ICS.confno,ICS.torque,angle FROM ICSDATA ICS JOIN PROGRAMTORQUE P ON ICS.CONFNO=P.CONFNO JOIN PARAM ON P.TORQUE=PARAM.TORQUE WHERE ICS.ID NOT IN(select top 0 id from icsdata order by id desc) order by ICS.id desc)
SELECT @cols=ISNULL(@cols+',','')+'torque'+LTRIM(sv.number)+',angle'+LTRIM(sv.number) FROM master.dbo.spt_values AS sv 
CROSS APPLY(SELECT  TOP 1 COUNT(0) AS cnt FROM #tab AS t GROUP BY torqueName,barcode ORDER BY COUNT(0) DESC) c
WHERE  sv.type='P' AND sv.number BETWEEN 1 AND c.cnt
PRINT @cols
SET @sql='
with #tab as (select top 100 P.torque as torqueName,PARAM.BOLTTYPE,opno,barcode,ICS.type,ICS.boltno,ICS.confno,ICS.torque,angle FROM ICSDATA ICS JOIN PROGRAMTORQUE P ON ICS.CONFNO=P.CONFNO JOIN PARAM ON P.TORQUE=PARAM.TORQUE WHERE ICS.ID NOT IN(select top 0 id from icsdata order by id desc) order by ICS.id desc)
SELECT * FROM  (
        SELECT   t.torqueName,t.BOLTTYPE,t.opno,t.barcode,t.type,t.boltno,t.confno,c.tile,c.val
        FROM (
            SELECT *,ROW_NUMBER()OVER(PARTITION BY torqueName,barcode,opno,type,boltno,confno ORDER BY GETDATE()) AS rn 
            FROM #tab
        ) AS t CROSS APPLY(VALUES(''torque''+LTRIM(rn),t.torque),(''angle''+LTRIM(rn),t.angle))c(tile,val)
) AS tt
PIVOT(MAX(val) FOR tile IN ('+@cols+'))p
ORDER BY p.barcode,p.torqueName DESC'
EXEC(@sql)
我用这个语句试了一下，非常好。但是有几个问题想要问一下各位高手 1、因为我的表是查询出的虚拟表，所以有一个with #tab as（）。。。。。在动态SQL中执行了一次，在SQL语句中也执行了一次，有什么方法简化一下吗？ 2、NULL值能否转成空字符串（出于视觉上的美观） 3、因为上位机用的是C# 不知道动态SQL部分怎么在软件上写出？
这个在过程里面写，把想要的数据处理好就行了，可以通过ISNULL函数将NULL转换为空字符串
脚跟着地 2017-07-03
  • 打赏
  • 举报
 回复 
DECLARE @cols VARCHAR(max),@sql VARCHAR(max)
with #tab as (select top 100 P.torque as torqueName,PARAM.BOLTTYPE,opno,barcode,ICS.type,ICS.boltno,ICS.confno,ICS.torque,angle FROM ICSDATA ICS JOIN PROGRAMTORQUE P ON ICS.CONFNO=P.CONFNO JOIN PARAM ON P.TORQUE=PARAM.TORQUE WHERE ICS.ID NOT IN(select top 0 id from icsdata order by id desc) order by ICS.id desc)
SELECT @cols=ISNULL(@cols+',','')+'torque'+LTRIM(sv.number)+',angle'+LTRIM(sv.number) FROM master.dbo.spt_values AS sv 
CROSS APPLY(SELECT  TOP 1 COUNT(0) AS cnt FROM #tab AS t GROUP BY torqueName,barcode ORDER BY COUNT(0) DESC) c
WHERE  sv.type='P' AND sv.number BETWEEN 1 AND c.cnt
PRINT @cols
SET @sql='
with #tab as (select top 100 P.torque as torqueName,PARAM.BOLTTYPE,opno,barcode,ICS.type,ICS.boltno,ICS.confno,ICS.torque,angle FROM ICSDATA ICS JOIN PROGRAMTORQUE P ON ICS.CONFNO=P.CONFNO JOIN PARAM ON P.TORQUE=PARAM.TORQUE WHERE ICS.ID NOT IN(select top 0 id from icsdata order by id desc) order by ICS.id desc)
SELECT * FROM  (
        SELECT   t.torqueName,t.BOLTTYPE,t.opno,t.barcode,t.type,t.boltno,t.confno,c.tile,c.val
        FROM (
            SELECT *,ROW_NUMBER()OVER(PARTITION BY torqueName,barcode,opno,type,boltno,confno ORDER BY GETDATE()) AS rn 
            FROM #tab
        ) AS t CROSS APPLY(VALUES(''torque''+LTRIM(rn),t.torque),(''angle''+LTRIM(rn),t.angle))c(tile,val)
) AS tt
PIVOT(MAX(val) FOR tile IN ('+@cols+'))p
ORDER BY p.barcode,p.torqueName DESC'
EXEC(@sql)
我用这个语句试了一下，非常好。但是有几个问题想要问一下各位高手 1、因为我的表是查询出的虚拟表，所以有一个with #tab as（）。。。。。在动态SQL中执行了一次，在SQL语句中也执行了一次，有什么方法简化一下吗？ 2、NULL值能否转成空字符串（出于视觉上的美观） 3、因为上位机用的是C# 不知道动态SQL部分怎么在软件上写出？
道素 2017-07-03
  • 打赏
  • 举报
 回复
更正下SQL，忘了将动态列换成变量了 

DECLARE @cols VARCHAR(max),@sql VARCHAR(max)
SELECT @cols=ISNULL(@cols+',','')+'torque'+LTRIM(sv.number)+',angle'+LTRIM(sv.number) FROM master.dbo.spt_values AS sv 
CROSS APPLY(SELECT  TOP 1 COUNT(0) AS cnt FROM #tab AS t GROUP BY torqueName,barcode ORDER BY COUNT(0) DESC) c
WHERE  sv.type='P' AND sv.number BETWEEN 1 AND c.cnt
PRINT @cols
SET @sql='
SELECT * FROM  (
        SELECT   t.torqueName,t.BOLTTYPE,t.opno,t.barcode,t.type,t.boltno,t.confno,c.tile,c.val
        FROM (
            SELECT *,ROW_NUMBER()OVER(PARTITION BY torqueName,barcode,opno,type,boltno,confno ORDER BY GETDATE()) AS rn 
            FROM #tab
        ) AS t CROSS APPLY(VALUES(''torque''+LTRIM(rn),t.torque),(''angle''+LTRIM(rn),t.angle))c(tile,val)
) AS tt
PIVOT(MAX(val) FOR tile IN ('+@cols+'))p
ORDER BY p.barcode,p.torqueName DESC'
EXEC(@sql)


  +------------+----------+------+---------+------+--------+--------+---------+--------+---------+--------+---------+--------+
| torqueName | BOLTTYPE | opno | barcode | type | boltno | confno | torque1 | angle1 | torque2 | angle2 | torque3 | angle3 |
+------------+----------+------+---------+------+--------+--------+---------+--------+---------+--------+---------+--------+
| 5/(20-30)  | M16*3    | OP10 | 111     | 111  | 3      | 5      | 2.6     | 1      | 2.1     | 1      | 2       | 1      |
| 2          | M16*2    | OP10 | 111     | 111  | 3      | 3      | 2.5     | 1      | 2.4     | 1      | 2.2     | 1      |
| 1.5        | M16*1    | OP10 | 111     | 111  | 2      | 2      | 2.4     | 1      | 2.2     | 1      | NULL    | NULL   |
| 5/(20-30)  | M16*3    | OP10 | 112     | 111  | 3      | 5      | 2.3     | 1      | 2.3     | 1      | 2.3     | 1      |
| 2          | M16*2    | OP10 | 112     | 111  | 3      | 3      | 2.3     | 1      | 2.3     | 1      | 2.3     | 1      |
| 1.5        | M16*1    | OP10 | 112     | 111  | 2      | 2      | 2.3     | 1      | 2.3     | 1      | NULL    | NULL   |
+------------+----------+------+---------+------+--------+--------+---------+--------+---------+--------+---------+--------+
道素 2017-07-03
  • 打赏
  • 举报
 回复
是要这样的吗？ 

CREATE TABLE #tab(torqueName VARCHAR(100),BOLTTYPE VARCHAR(100),opno VARCHAR(100),barcode VARCHAR(100),type VARCHAR(100),boltno VARCHAR(100),confno VARCHAR(100),torque FLOAT,angle FLOAT)
INSERT INTO #tab
SELECT '5/(20-30)','M16*3','OP10','111','111','3','5','2.60','1.00' UNION ALL
SELECT '5/(20-30)','M16*3','OP10','111','111','3','5','2.10','1.00' UNION ALL
SELECT '5/(20-30)','M16*3','OP10','111','111','3','5','2.00','1.00' UNION ALL
SELECT '2','M16*2','OP10','111','111','3','3','2.50','1.00' UNION ALL
SELECT '2','M16*2','OP10','111','111','3','3','2.40','1.00' UNION ALL
SELECT '2','M16*2','OP10','111','111','3','3','2.20','1.00' UNION ALL
SELECT '1.5','M16*1','OP10','111','111','2','2','2.40','1.00' UNION ALL
SELECT '1.5','M16*1','OP10','111','111','2','2','2.20','1.00' UNION ALL
SELECT '5/(20-30)','M16*3','OP10','112','111','3','5','2.30','1.00' UNION ALL
SELECT '5/(20-30)','M16*3','OP10','112','111','3','5','2.30','1.00' UNION ALL
SELECT '5/(20-30)','M16*3','OP10','112','111','3','5','2.30','1.00' UNION ALL
SELECT '2','M16*2','OP10','112','111','3','3','2.30','1.00' UNION ALL
SELECT '2','M16*2','OP10','112','111','3','3','2.30','1.00' UNION ALL
SELECT '2','M16*2','OP10','112','111','3','3','2.30','1.00' UNION ALL
SELECT '1.5','M16*1','OP10','112','111','2','2','2.30','1.00' UNION ALL
SELECT '1.5','M16*1','OP10','112','111','2','2','2.30','1.00'
DECLARE @cols VARCHAR(max),@sql VARCHAR(max)
SELECT @cols=ISNULL(@cols+',','')+'torque'+LTRIM(sv.number)+',angle'+LTRIM(sv.number) FROM master.dbo.spt_values AS sv 
CROSS APPLY(SELECT  TOP 1 COUNT(0) AS cnt FROM #tab AS t GROUP BY torqueName,barcode ORDER BY COUNT(0) DESC) c
WHERE  sv.type='P' AND sv.number BETWEEN 1 AND c.cnt
PRINT @cols
SET @sql='
SELECT * FROM  (
        SELECT   t.torqueName,t.BOLTTYPE,t.opno,t.barcode,t.type,t.boltno,t.confno,c.tile,c.val
        FROM (
            SELECT *,ROW_NUMBER()OVER(PARTITION BY torqueName,barcode,opno,type,boltno,confno ORDER BY GETDATE()) AS rn 
            FROM #tab
        ) AS t CROSS APPLY(VALUES(''torque''+LTRIM(rn),t.torque),(''angle''+LTRIM(rn),t.angle))c(tile,val)
) AS tt
PIVOT(MAX(val) FOR tile IN (torque1,angle1,torque2,angle2,torque3,angle3))p
ORDER BY p.barcode,p.torqueName DESC'
EXEC(@sql)


torqueName	BOLTTYPE	opno	barcode	type	boltno	confno	torque1	angle1	torque2	angle2	torque3	angle3
5/(20-30)	M16*3	OP10	111	111	3	5	2.6	1	2.1	1	2	1
2	M16*2	OP10	111	111	3	3	2.5	1	2.4	1	2.2	1
1.5	M16*1	OP10	111	111	2	2	2.4	1	2.2	1	NULL	NULL
5/(20-30)	M16*3	OP10	112	111	3	5	2.3	1	2.3	1	2.3	1
2	M16*2	OP10	112	111	3	3	2.3	1	2.3	1	2.3	1
1.5	M16*1	OP10	112	111	2	2	2.3	1	2.3	1	NULL	NULL
脚跟着地 2017-07-02
  • 打赏
  • 举报
 回复
表结构就是第一张表的结构，后面的是csv文件，您可以直接导入到数据库中。因为我的表是join后查询出来的，所以不能给你直接导出一个sql语句了，和经典的成绩有些不一样。
zhouyuehai1978 2017-07-02
  • 打赏
  • 举报
 回复
楼主参考下这个问题，和你很像，我在7楼做了答案 http://bbs.csdn.net/topics/392085102
LongRui888 2017-07-02
  • 打赏
  • 举报
 回复
有表结构，和测试数据吗? 这个可以通过xml path来合并数据的例子： 
create table test4 (name varchar(10),mytype varchar(10),cj int )  
  
insert into test4   
values('张三','语文',83),  
('张三','数学',65),  
('张三','物理',85),  
('李四','语文',73),  
('李四','数学',69),  
('李四','物理',93)  
  
  
select name,  
       stuff((select ','+mytype from test4 t4 where t4.name = test4.name for xml path('')),1,1,'') as mytype,  
       stuff((select ','+cast(cj as varchar) from test4 t4 where t4.name = test4.name for xml path('')),1,1,'') as cj  
from test4  
group by name  
/*  
name    mytype  cj  
李四  语文,数学,物理    73,69,93  
张三  语文,数学,物理    83,65,85
oracle常用函数
在SQL中有两种函数一种是单行函数，一种是多行函数.在sql与pl/sql中都自带了很多类型的函数,比如有字符、数字、日期、转换和混合型等多种函数用于处理单行数据,因此这些都被称为单行函数.这些函数都可以被用于select...
精通SQL--结构化查询语言详解
第1章 数据库与sql基础 1 1.1 数据库的基本概念 1 1.1.1 数据库的由来 1 1.1.2 数据库系统的概念 3 1.2 数据库系统的结构、组成及工作流程 3 1.2.1 数据库的体系结构 3 1.2.2 数据库系统的组成 4 1.2.3 ...
2019小白SQL入门（零基础60分钟含SQL语法问答精彩）
零基础60分钟含SQL语法问答精彩
Oracle_PLSQL_语法详细手册
1. 转换函数: 12 2. 日期函数 16 3. 字符函数 20 4. 数值函数 28 5. 单行函数: 33 6. 多行函数 35 第二部分 PL/SQL语法部分 41 一、 PL/SQL语言简介 41 二、 变量说明 48 三、 PL/SQL控制程序流 52 四、 存储过程 54...
oracle(数据类型函数).
里面有详细的oracle函数 1、Oracle数据类型 2、Oracle函数分为单行函数和多行函数两大类（sql_function） 单行函数分类 字符函数 数值函数 日期函数 转换函数 通用函数 函数嵌套 分组函数：集合操作符(了解) 实现...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章