SQLSERVER两种拼接

[月似人伤]

SELECT
packageID,
STUFF(
(
SELECT
',' + name
FROM
--BEGIN
(
SELECT
PACKAGEITEM.packageID,
PRODUCT.name,
PRODUCT.productid
FROM
T_NTP_SAILINFO_PACKAGE_ITEMS AS PACKAGEITEM
LEFT JOIN T_NTP_BASE_PRODUCT AS PRODUCT ON PRODUCT.productid = PACKAGEITEM.productID AND PRODUCT.DelFlag = 'N'
WHERE PACKAGEITEM.DelFlag='N'
) a
--END
WHERE
packageID = associated.packageID
FOR XML PATH ('')

),1,1,'') AS productNames
FROM
--BEGIN
(
SELECT
PACKAGEITEM.packageID,
PRODUCT.name,
PRODUCT.productid
FROM
T_NTP_SAILINFO_PACKAGE_ITEMS AS PACKAGEITEM
LEFT JOIN T_NTP_BASE_PRODUCT AS PRODUCT ON PRODUCT.productid = PACKAGEITEM.productID AND PRODUCT.DelFlag = 'N'
WHERE PACKAGEITEM.DelFlag='N'
)
--END
AS associated
GROUP BY
packageID


------------------------------2
WITH tab AS(
SELECT
'张三' AS name,
'语文' AS kemu,
81 AS fenshu
UNION
SELECT
'张三' AS name,
'数学' AS kemu,
72 AS fenshu
union
SELECT
'李四' AS name,
'语文' AS kemu,
76 AS fenshu
UNION
SELECT
'李四' AS name,
'数学' AS kemu,
90 AS fenshu
union
SELECT
'王五' AS name,
'语文' AS kemu,
81 AS fenshu
UNION
SELECT
'王五' AS name,
'数学' AS kemu,
100 AS fenshu
)



-- SELECT tab.*,temp.fenshu FROM tab
-- LEFT JOIN (
-- select name,sum(fenshu)/2 as fenshu from tab
-- GROUP BY name
-- ) temp ON tab.name= temp.name
-- where tab.fenshu < temp.fenshu
--
--
-- SELECT tab.*,
-- (
-- CASE
-- WHEN tab.fenshu <80
-- then '合格'
-- WHEN tab.fenshu >=80 and tab.fenshu <90
-- then '中等'
-- WHEN tab.fenshu >=90
-- then '优秀'
-- ELSE ''
-- END
-- )
-- FROM tab
--
--
SELECT
name as 姓名,
MAX(
CASE
WHEN kemu='语文' THEN
fenshu
END
) AS 语文,
MAX(
CASE
WHEN kemu='数学' THEN
fenshu
END
) AS 数学

FROM
tab
GROUP BY name