December 17, 2021 at 11:57 am
hi,
col "R" has report no like "R1" "R2" ETC COL "D" AS Direction like "D" "D1". direction can be more than one in one report.
--uniqueness of record is col R + D
result needed is all directions of the table IN one SCALAR column with all comma seprated and ";" seprated like FOLLOWING.
'D,D1;D,D1,D2' in order of "R" COL that is first i need DIRECTIONS of report "R1" THEN "R2" AND DIRECTIONs ALSO SHOULD BE IN --ORDER OF "D" COL
SELECT * INTO #T FROM (
SELECT 'R1' R , 'D' D
UNION
SELECT 'R1' R , 'D1' D
UNION
SELECT 'R2' R , 'D' D
UNION
SELECT 'R2' R , 'D1' D
UNION
SELECT 'R2' R , 'D2' D
) T1
SELECT * FROM #T
DROP TABLE #T
December 17, 2021 at 12:17 pm
Such a shame you're on 2012. It's time to upgrade! In 2017+, this solution works:
WITH r1
AS (SELECT R
,Result1 = STRING_AGG(D, ',') WITHIN GROUP(ORDER BY D)
FROM #T
GROUP BY R)
SELECT Result2 = STRING_AGG(r1.Result1, ';') WITHIN GROUP(ORDER BY r1.R)
FROM r1;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 17, 2021 at 4:31 pm
See the following where Wayne Sheffield teaches the ropes on how to do this prior to having String_Agg() available.
https://www.sqlservercentral.com/articles/creating-a-comma-separated-list-sql-spackle
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2021 at 5:25 am
we tried to upgrade to 2019, but unfortunately, MS has put some restrictions like they have converted scalar functions to inline function by default and that throws an error, so we had to switch off that defaulting (some concatenation is going on in our functions) , be we do want to convert all function so that we can use the new defaulting as function slow down the query. and we are in process of converting all functions. then we will release it on mains, now 2019 is on QA server.
December 18, 2021 at 1:52 pm
we tried to upgrade to 2019, but unfortunately, MS has put some restrictions like they have converted scalar functions to inline function by default and that throws an error ....
For my own interest, can you go into more detail about this, please? I know that the change to the cardinality estimator caused some query slowness issues, but I was not aware of errors.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 18, 2021 at 7:38 pm
we tried to upgrade to 2019, but unfortunately, MS has put some restrictions like they have converted scalar functions to inline function by default and that throws an error, so we had to switch off that defaulting (some concatenation is going on in our functions) , be we do want to convert all function so that we can use the new defaulting as function slow down the query. and we are in process of converting all functions. then we will release it on mains, now 2019 is on QA server.
You may have to bite the proverbial bullet there. And, it's probably for the better.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2021 at 5:22 am
xml solution and function solution ex, calling scalar funtion i had . apart from these if any thing is there then pls tell me so that i stop finding and start using above methods. in (2012)
December 21, 2021 at 9:46 am
xml solution and function solution ex, calling scalar funtion i had . apart from these if any thing is there then pls tell me so that i stop finding and start using above methods. in (2012)
The XML way is probably your best choice, IMO.
Please answer my question about errors when you can.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply