How to create view or function on XML PATH query?
I am using SSMS 2008 with the following query:
DECLA开发者_开发问答RE @TestData TABLE
(
address_desc NVARCHAR(100) NULL
,people_id UNIQUEIDENTIFIER NULL
);
INSERT @TestData
SELECT a.address_desc, a.people_id
FROM dbo.address_view a
SELECT a.people_id,
(SELECT SUBSTRING(
(SELECT ';'+b.address_desc
FROM @TestData b
WHERE a.people_id = b.people_id
FOR XML PATH(''))
,2
,4000)
) GROUP_CONCATENATE
FROM @TestData a
GROUP BY a.people_id
This query works, but I want to make this into a view or function so that I can call it from different stored procs. How can I do this? From what I understand, variables cannot be declared in VIEW statements.
Hong, here is my updated query based on your advice which gives me errors:
DECLARE @TestData TABLE
(
address_desc NVARCHAR(100) NULL
,people_id UNIQUEIDENTIFIER NULL
);
INSERT @TestData
SELECT a.address_desc, a.people_id FROM dbo.address_view a
SELECT a.people_id,
(SELECT address_desc, people_id FROM dbo.address_view),
(SELECT SUBSTRING(
(SELECT ';'+b.address_desc
FROM @TestData b
WHERE a.people_id = b.people_id
FOR XML PATH(''))
,2
,4000)
) GROUP_CONCATENATE
FROM @TestData a
GROUP BY a.people_id
In your last select query replace @TestData with subquery (SELECT address_desc, people_id FROM dbo.address_view)
, and then get rid of temp table @TestData.
Try this:
Create View YourView As
SELECT a.people_id,
(SELECT SUBSTRING(
(SELECT ';'+b.address_desc
FROM (SELECT address_desc, people_id FROM dbo.address_view) b
WHERE a.people_id = b.people_id
FOR XML PATH(''))
,2
,4000)
) GROUP_CONCATENATE
FROM (SELECT address_desc, people_id FROM dbo.address_view) a
GROUP BY a.people_id
精彩评论