转置查询创建节点 (SQL Server 2008) [英] Transpose query creates nodes (SQL Server 2008)
问题描述
我终于找到了要执行的查询,以获取一行中一个内容的所有 id(逗号分隔).
I finally found the query to execute to get all ids (comma separated) for one content in one row.
以下查询成功了:
您不需要查看查询,因为它已经做了它应该做的事情.
You don't need to look at the query because it already does what it should do.
SELECT
taxonomy_item_id,
SUBSTRING(
(SELECT ', ' + CAST(taxonomy_id AS varchar) AS Expr1
FROM taxonomy_item_tbl AS t2
WHERE (t1.taxonomy_item_id = taxonomy_item_id) AND (taxonomy_language_id = 2067)
ORDER BY taxonomy_item_id, taxonomy_id FOR XML PATH('')
), 1, 1000) AS taxonomy_ids
FROM
taxonomy_item_tbl AS t1
WHERE
(taxonomy_language_id = 2067) AND (taxonomy_item_id = 180555)
GROUP BY
taxonomy_item_id
唯一的问题是我得到的数据结果:
The only problem is the data result I get:
180555 | <Expr1>, 404</Expr1><Expr1>, 405</Expr1><Expr1>, 723</Expr1><Expr1>, 1086</Expr1><Expr1>, 1087</Expr1><Expr1>, 1118</Expr1><Expr1>, 1124</Expr1><Expr1>, 1126</Expr1>
我不需要
节点.有没有办法删除这个?如果我在查询中删除 AS Expr1
然后它会自动添加回来
I don't need the <Expr1>
nodes. Is there a way to delete this? If I delete AS Expr1
in the query then it is automatically added back
谢谢
推荐答案
如果您不想要
- 那么就不要要求它!
If you don't want the <Expr1>
- then just don't ask for it!
你有:
(SELECT ', ' + CAST(taxonomy_id AS varchar) AS Expr1
那个 AS Expr1
导致
被添加 - 所以不要在那里有那个表达式.
That AS Expr1
causes the <Expr1>
to be added - so just don't have that expression there.
试试
SELECT
taxonomy_item_id,
SUBSTRING(
(SELECT ', ' + CAST(taxonomy_id AS VARCHAR)
FROM dbo.taxonomy_item_tbl AS t2
WHERE t1.taxonomy_item_id = taxonomy_item_id
AND taxonomy_language_id = 2067
ORDER BY taxonomy_item_id, taxonomy_id
FOR XML PATH('')
), 1, 1000) AS taxonomy_ids
FROM
dbo.taxonomy_item_tbl AS t1
WHERE
taxonomy_language_id = 2067
AND taxonomy_item_id = 180555
GROUP BY
taxonomy_item_id
这篇关于转置查询创建节点 (SQL Server 2008)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!