Respected Techie,
can anyone please help me
how can i store the cte result in a table. each time i run the script table recreated with fresh data.
i tried like this but this is giving error.
;WITH DataSource AS
(
-- columns on which we are grouping
SELECT DISTINCT
[BaseVehicleID]
,[ParttypeID]
,[SubModelID]
FROM @TABLE
)
IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL
DROP TABLE dbo.Scores;
SELECT *
into Scores FROM DataSource DS
CROSS APPLY
(
SELECT STUFF
(
(
SELECT DISTINCT ';' +[Params]
FROM @TABLE T
WHERE
T.[BaseVehicleID] = DS.[BaseVehicleID]
AND T.[ParttypeID] = DS.[ParttypeID]
AND T.[SubModelID] = DS.[SubModelID]
AND [Params] <> ''
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1
,1
,''
)
) DS1([Params])
CROSS APPLY
(
SELECT STUFF
(
(
SELECT DISTINCT ';' +[Note]
FROM @TABLE T
WHERE T.[BaseVehicleID] = DS.[BaseVehicleID]
AND T.[ParttypeID] = DS.[ParttypeID]
AND T.[SubModelID] = DS.[SubModelID]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1
,1
,''
)
) DS2 ([Note])
Thanks.....