how to make comm seprated list with control break. in sql 2012

Question

how to make comm seprated list with control break. in sql 2012

rajemessage 14195

Hall of Fame

Points: 3315
More actions
December 17, 2021 at 11:57 am

#3964752

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rajemessage 14195 Hall of Fame Points: 3315 More actions · Answer 3

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.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

rajemessage 14195 wrote:

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

rajemessage 14195 wrote:

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rajemessage 14195 Hall of Fame Points: 3315 More actions · Answer 6

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)

This reply was modified 2 years, 11 months ago by rajemessage 14195.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

rajemessage 14195 wrote:

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin