Below is the structure of table I have: -
以下是我的表格结构: -
Table T1
C1 C2 C3
----------
X P A
X P B
Y Q C
Y Q D
Desired output: -
期望的输出: -
C1 C2 C3
------------
X P A,B
Y Q C,D
Note: - I know i can do the same with For XML('') with group by on C1 and C2, but the main problem in my case is that the table T1 here must be a physical table object (either permanent or temp or table var or CTE) in DB. But in my case it's a derived table and when i am using the below query it's saying invalid object. In my case it's not good to replace the derived table with temp# tables or fixed tables or even with CTE or table variable because it will take a great effort.
注意: - 我知道我可以对带有分组C1和C2的For XML('')做同样的事情,但我的主要问题是这里的表T1必须是物理表对象(永久或临时或DB中的表var或CTE)。但在我的情况下,它是一个派生表,当我使用下面的查询时,它说的是无效的对象。在我的情况下,用temp#表或固定表或甚至用CTE或表变量替换派生表是不好的,因为它需要付出很大的努力。
SELECT
b.C1, b.C2, Stuff((',' + a.C3 from t1 a where a.c1 = b.c1 for XML PATH('')),1,1,'') FROM
T1 b group by b.c1,b.c2
I did not have T1 as fixed table. Please consider it as derived table only.
我没有T1作为固定表。请将其视为派生表。
I need the solution with existing derived table. Please help.
我需要现有派生表的解决方案。请帮忙。
Below is the query with derived table: - Please consider this only as a demo query. It's not as simple as given below and a lot of calculations have done to get the derived tables and 4 levels of derived tables have been used.
下面是带派生表的查询: - 请将此视为演示查询。它并不像下面给出的那么简单,并且已经进行了大量计算以获得派生表并且已经使用了4级派生表。
SELECT C1, C2, Stuff((',' + a.C3 from A B where a.c1 = b.c1 for XML PATH('')),1,1,'')
FROM
(
SELECT C1, C2, C3 FROM T1 WHERE C1 IS NOT NULL--and a lot of calculation also
)A
Please mind that T1 is not just below one step, in my case T1 the actual physical table is 4 level downs by derived tables.
请注意,T1不仅仅低于一步,在我的情况下T1实际物理表是由派生表降低4级。
1 个解决方案
#1
3
If you can post the query the produces derived table, we can help you work it out, but as of the moment try substituting table1 with the derived query.
如果您可以发布查询生成派生表,我们可以帮助您解决问题,但是到目前为止尝试用派生查询替换table1。
;WITH Table1
AS
(
SELECT C1, C2, C3 FROM T1 WHERE C1 IS NOT NULL--and a lot of calculation also
)
SELECT
C1,C2,
STUFF(
(SELECT ',' + C3
FROM Table1
WHERE C1 = a.C1 AND C2 = a.C2
FOR XML PATH (''))
, 1, 1, '') AS NamesList
FROM Table1 AS a
GROUP BY C1,C2
#1
3
If you can post the query the produces derived table, we can help you work it out, but as of the moment try substituting table1 with the derived query.
如果您可以发布查询生成派生表,我们可以帮助您解决问题,但是到目前为止尝试用派生查询替换table1。
;WITH Table1
AS
(
SELECT C1, C2, C3 FROM T1 WHERE C1 IS NOT NULL--and a lot of calculation also
)
SELECT
C1,C2,
STUFF(
(SELECT ',' + C3
FROM Table1
WHERE C1 = a.C1 AND C2 = a.C2
FOR XML PATH (''))
, 1, 1, '') AS NamesList
FROM Table1 AS a
GROUP BY C1,C2