Transposing rows into columns and concatenate rows of strings into one string with a specified separ-CodePudding

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

CodePudding user response：

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