July 28, 2023 at 9:16 am
hi,
drop table #t
create table #t (tid int,c1 varchar(35),c2 varchar(35),c3 varchar(35))
insert into #t (tid,c1,c2,c3) values (1,'t','t','c')
insert into #t (tid,c1,c2,c3) values (2,'r','r','r')
insert into #t (tid,c1,c2,c3) values (3,'r','r1','r2')
insert into #t (tid,c1,c2,c3) values (3,'r','r1','r1')
select
c1 + case when c1=c2 then '' else ','+c2 end + case when c1=c3 then '' else ','+c3 end
from #t
problem is in fourth row, so I found that I have to find permutation and combination to do it, and on the other hand I have 13 cols.
So I thought to unpivot and then find the distinct and thereafter pivot it again. Which is lengthy.
So is there any easy way to do it?
Yours sincerely
July 28, 2023 at 10:26 am
This was removed by the editor as SPAM
July 28, 2023 at 12:08 pm
This was removed by the editor as SPAM
July 28, 2023 at 2:59 pm
So I thought to unpivot and then find the distinct and thereafter pivot it again. Which is lengthy.
Yes imo you've got the right ideas unpivot and re-pivot is what's happening. You could use STRING_AGG to do the re-pivoting and then it's a question of being careful about the ordering. Maybe something like this. I added 2 additional rows to the input to demonstrate the (questionable) necessity to include the 'c' column of the unpvt_cte in the ORDER BY of the STRING_AGG function.
drop table if exists #t;
go
create table #t (tid int,c1 varchar(35),c2 varchar(35),c3 varchar(35));
insert into #t (tid,c1,c2,c3) values
(1,'t','t','c'),
(2,'r','r','r'),
(3,'r','r1','r2'),
(3,'r','r1','r1'),
(4,'r','r1','r2'),
(4,'r','r2','r1');
with unpvt_cte as (
select t.tid, unpvt.c, min(unpvt.ord) min_ord
from #t t
cross apply (values (1, t.c1),
(2, t.c2),
(3, t.c3)) unpvt(ord, c)
group by t.tid, unpvt.c)
select tid, string_agg(c, ',') within group(order by min_ord, c) new_string
from unpvt_cte
group by tid
order by tid;
Results
tid new_string
1 t,c
2 r
3 r,r1,r2
4 r,r1,r2
Without the c column of the unpvt_cte the results
tid new_string
1 t,c
2 r
3 r,r1,r2
4 r,r2,r1
So the ordering in this example depends on: 1) the left/right appearance by column number, and 2) the alphabetical ordering of the data elements
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 29, 2023 at 12:03 pm
This was removed by the editor as SPAM
August 2, 2023 at 12:45 pm
This was removed by the editor as SPAM
August 4, 2023 at 10:39 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply