SELECT STUFF( ( SELECT ',' + CONVERT ( VARCHAR, CASE WHEN ISNUMERIC( REPLACE( t2.pp, ',', '' ) ) = 1 THEN CAST ( REPLACE( t2.pp, ',', '' ) AS FLOAT ) END ) FROM formtable_main_43_dt1 t2 WHERE t1.id = t2.mainid FOR XML PATH ( '' ) ), 1, 1, '' ) AS '品牌' FROM formtable_main_43 t1为第一段代码， SELECT STUFF( ( SELECT ',' + CONVERT ( VARCHAR, CASE WHEN ISNUMERIC( REPLACE( t2.hsdj, ',', '' ) ) = 1 THEN CAST ( REPLACE( t2.hsdj, ',', '' ) AS FLOAT ) END ) FROM formtable_main_43_dt1 t2 WHERE t1.id = t2.mainid FOR XML PATH ( '' ) ), 1, 1, '' ) AS '采购单价' FROM formtable_main_43 t1为第二段代码，如何整合成一个查询

可以使用以下查询来整合这两个查询：

SELECT
STUFF(
(
SELECT
',' + CONVERT (
VARCHAR,
CASE
WHEN ISNUMERIC( REPLACE( t2.pp, ',', '' ) ) = 1 THEN
CAST ( REPLACE( t2.pp, ',', '' ) AS FLOAT )
END
)
FROM
formtable_main_43_dt1 t2
WHERE
t1.id = t2.mainid
FOR XML PATH ( '' )
),
1,
1,
''
) AS '品牌',
STUFF(
(
SELECT
',' + CONVERT (
VARCHAR,
CASE
WHEN ISNUMERIC( REPLACE( t2.hsdj, ',', '' ) ) = 1 THEN
CAST ( REPLACE( t2.hsdj, ',', '' ) AS FLOAT )
END
)
FROM
formtable_main_43_dt1 t2
WHERE
t1.id = t2.mainid
FOR XML PATH ( '' )
),
1,
1,
''
) AS '采购单价'
FROM
formtable_main_43 t1

相关问题

SELECT t1.id, STUFF( ( SELECT ',' + CONVERT ( VARCHAR, CASE WHEN ISNUMERIC( REPLACE( t2.hsdj, ',', '' ) ) = 1 THEN CAST ( REPLACE( t2.hsdj, ',', '' ) AS FLOAT ) END ) FROM formtable_main_43_dt1 t2 WHERE t1.id = t2.mainid FOR XML PATH ( '' ) ), 1, 1, '' ) AS MergedValues FROM formtable_main_43 t1 INNER JOIN formtable_main_43_dt1 t2 ON t1.id = t2.mainid GROUP BY t1.id需要展示更多T1中字段，该怎么该

如果您需要在结果中展示更多的 t1 中的字段，您可以在 SELECT 语句中添加这些字段，同时在 GROUP BY 语句中也需要包含这些字段。例如，如果您想要展示 t1 中的字段 t1.field1 和 t1.field2，您可以将 SELECT 语句修改为：

SELECT t1.id, t1.field1, t1.field2, 
    STUFF(
        (
            SELECT ',' + CONVERT (VARCHAR,
                CASE 
                    WHEN ISNUMERIC(REPLACE(t2.hsdj, ',', '')) = 1 THEN 
                        CAST(REPLACE(t2.hsdj, ',', '') AS FLOAT)
                END
            )
            FROM formtable_main_43_dt1 t2
            WHERE t1.id = t2.mainid 
            FOR XML PATH('')
        ), 1, 1, ''
    ) AS MergedValues
FROM formtable_main_43 t1 
INNER JOIN formtable_main_43_dt1 t2 ON t1.id = t2.mainid 
GROUP BY t1.id, t1.field1, t1.field2

请注意，根据您的需求，您可能还需要添加其他的 t1 字段。

SELECT t1.id, STUFF((SELECT ',' + CONVERT(VARCHAR, CASE WHEN ISNUMERIC(REPLACE(t2.hsdj, ',', '')) = 1 THEN CAST(REPLACE(t2.hsdj, ',', '') AS FLOAT) ELSE NULL END) FROM formtable_main_43_dt1 t2 WHERE t1.id = t2.mainid FOR XML PATH ('')), 1, 1, '') AS MergedValues, STUFF((SELECT ',' + CAST(CAST(t1.htwj AS VARBINARY(MAX)) AS VARCHAR(MAX)) FROM formtable_main_43 t1 WHERE t1.id = t2.mainid FOR XML PATH ('')), 1, 1, '') AS ImageValues FROM formtable_main_43 t1 INNER JOIN formtable_main_43_dt1 t2 ON t1.id = t2.mainid GROUP BY t1.id t1.htwj;提示“t1”附近有语法错误

根据你提供的 SQL 语句，应该是这个部分出现了问题：

SELECT t1.id, STUFF((SELECT ',' + CONVERT(VARCHAR, CASE WHEN ISNUMERIC(REPLACE(t2.hsdj, ',', '')) = 1 THEN CAST(REPLACE(t2.hsdj, ',', '') AS FLOAT) ELSE NULL END) FROM formtable_main_43_dt1 t2 WHERE t1.id = t2.mainid FOR XML PATH ('')), 1, 1, '') AS MergedValues, STUFF((SELECT ',' + CAST(CAST(t1.htwj AS VARBINARY(MAX)) AS VARCHAR(MAX)) FROM formtable_main_43 t1  WHERE t1.id = t2.mainid FOR XML PATH ('')), 1, 1, '') AS ImageValues FROM formtable_main_43 t1 INNER JOIN formtable_main_43_dt1 t2 ON t1.id = t2.mainid  GROUP BY t1.id t1.htwj;

在 GROUP BY 子句中，应该使用逗号将分组的列分隔开，而不是空格。所以你需要将 `GROUP BY t1.id t1.htwj` 改为 `GROUP BY t1.id, t1.htwj`。