SqlServerl的行转列

目录

• 关键词：SqlServer
  • 问题背景
  • 场景1
  • 场景2

关键词：SqlServer

问题背景

1. hql语句不熟悉，多表联查的时候都是在业务层在写其他的方法去查。（感觉真的好菜啊，但是还是要直面现实）。
2. 测试的时候发现数据过滤的不对。有很多所谓的”重复"数据。以我项目中的两个场景为例。

场景1

如图化学品和类型是一对多的关系。如果按普通的左关联查询查到的数据应该是，每一个化学品有几个类型就会有几条数据。所以这里用到的是多行转多列

--多行转多列
SELECT O.CHEMICAL_ID,O.CAS,O.JC,
max(CASE O.DETAILS_NAME WHEN '重点监管' THEN O.TZLBBM ELSE 0 end)重点监管,
max(CASE O.DETAILS_NAME WHEN '易制毒' THEN O.TZLBBM ELSE 0 end)易制毒,
max(CASE O.DETAILS_NAME WHEN '易制爆' THEN O.TZLBBM ELSE 0 end)易制爆,
max(CASE O.DETAILS_NAME WHEN '剧毒化学品' THEN O.TZLBBM ELSE 0 end)剧毒化学品,
max(CASE O.DETAILS_NAME WHEN '高毒物品' THEN O.TZLBBM ELSE 0 end)高毒物品
FROM (SELECT C.*,E.DETAILS_NAME,T.TZLBBM FROM TB_BASE_CHEMICAL AS C
LEFT JOIN TB_BASE_CHEMICAL_TZLB AS T ON T.CHEMICAL_ID=C.CHEMICAL_ID
LEFT JOIN TB_HSE_DICTIONARY_DETAILS AS E ON E.DETAILS_CODE=T.TZLBBM AND E.COM_DEPTCODE='20000000')AS O
GROUP BY O.CHEMICAL_ID,O.CAS,O.JC

效果如图

场景2

这里的化学品和类别也是一对多的关系，用到的多行转一列。
用到了 stuff(select ',' + fieldname from tablename for xml path('')),1,1,'') 函数
这一整句的作用是将多行fieldname字段的内容串联起来，用逗号分隔。
SELECT
qywhpglid ,typeName = ( STUFF(( SELECT ',' + typeName
FROM (
SELECT
a.QYWHPGL_ID AS qywhpglid,
g.DETAILS_NAME AS typeName
FROM
TB_HSE_FOUN_QYWHPGL a
INNER JOIN TB_BASE_CHEMICAL c ON a.WHPBM = c.CHECODE
INNER JOIN TB_BASE_CHEMICAL_TZLB f ON f.CHEMICAL_ID = c.CHEMICAL_ID
INNER JOIN TB_HSE_DICTIONARY_DETAILS g ON f.TZLBBM = g.DETAILS_CODE and g.ORGCODE='20000000'
WHERE
a.U_VALIDATE = '1'
)AS R
WHERE R.qywhpglid = Test.qywhpglid
FOR XML PATH('')), 1, 1, '') )

FROM
(

SELECT
a.QYWHPGL_ID AS qywhpglid,
g.DETAILS_NAME AS typeName
FROM
TB_HSE_FOUN_QYWHPGL a
INNER JOIN TB_BASE_CHEMICAL c ON a.WHPBM = c.CHECODE
INNER JOIN TB_BASE_CHEMICAL_TZLB f ON f.CHEMICAL_ID = c.CHEMICAL_ID
INNER JOIN TB_HSE_DICTIONARY_DETAILS g ON f.TZLBBM = g.DETAILS_CODE and g.ORGCODE='20000000'
WHERE
a.U_VALIDATE = '1'

) AS Test
GROUP BY qywhpglid

注意：斜体加粗部分是两个表，它们的主键和要合并的行是一样的。如果不加上主键相等（标黄部分就会出现重复数据）