Transposing rows into columns and concatenate rows of strings into one string with a specified separator

Question

I am trying to transpose rows into columns and concatenate rows of strings into one string with a specified separator.

My table:

type	name	num	age	dateA	DateB	PRICE
A	Aziz	1	12	2012-01-11 00:00:00.000	2012-01-11 00:00:00.000	129.00
B	Aziz	1	34	2012-01-11 00:00:00.000	2012-01-11 00:00:00.000	100.00
C	Aziz	0	20	2012-01-11 00:00:00.000	2012-01-11 00:00:00.000	349.00
D	Aziz	1	90	2012-01-11 00:00:00.000	2012-01-11 00:00:00.000	222.00

The result I'd like to have:

name	A	B	C	D
Aziz	1, 12,2012-01-11,2012-01-11,129.00	1, 34, 2012-01-11, 2012-01-11, 100.00	0,20,2012-01-11, 2012-01-11, 349.00	1,90,2012-01-11, 2012-01-11,222.00

John Cappelletti · Accepted Answer · 2022-11-02 19:42:33Z

1

EDIT - Removed the CROSS APPLY. I was going to use a conditional aggregation and decided to go straight PIVOT.

Select *
 From  (
        Select Name 
              ,Type
              ,Str = concat(num,',',age,',',convert(date,dateA),',',convert(date,dateB),',',Price)
         From YourTable A
       ) src
 Pivot ( max(Str) for Type in ([A],[B],[C],[D] ) ) pvt

Results

edited Nov 2, 2022 at 19:42

answered Nov 2, 2022 at 19:04

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Aziz bouchtaoui Over a year ago

but CROSS APPY its just for agregate in my case i need to transpose rows into columns also

John Cappelletti Over a year ago

@Azizbouchtaoui The CROSS APPLY creates the string per record. The PIVOT transposes the rows into columns. What am I missing ?

Aziz bouchtaoui Over a year ago

SORRY, i was wrong, its OK working fine, thnak you Mr you save my life <3

Aziz bouchtaoui Over a year ago

Hi John; please can you take a look to this question : stackoverflow.com/q/74302268/19123196

John Cappelletti Over a year ago

@Azizbouchtaoui apologies for the late response (currently on the west coast). here is a a dynamic version dbfiddle.uk/AtsMGngH ... Rhythm's solution is good, but this is a more data driven

Collectives™ on Stack Overflow

Transposing rows into columns and concatenate rows of strings into one string with a specified separator

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related