I have the below table
USE tempdb
--Create a table
CREATE TABLE T
(
Model VARCHAR(20),
ManfDate DATE,
Diaset VARCHAR(20)
)
--Insert the records
INSERT INTO T VALUES
('AA1','02/02/1981','AB1')
,('AA2','02/02/1981','AB2')
,('AA3','02/02/1981','AB3')
,('AA3','02/04/1981','AB4')
,('AA4','02/10/1981','AB5')
--Project the record
SELECT * FROM T
--DROP THE TABLE
DROP TABLE T
/* OUTPUT
Model ManfDate Diaset
AA1 1981-02-02 AB1
AA2 1981-02-02 AB2
AA3 1981-02-02 AB3
AA3 1981-02-04 AB4
AA4 1981-02-10 AB5
*/
Expected Output SingleRow
-----------------------------------------------------------------------------------------------------------
02 Feb (AA1-Model1,AA2-Model2,AA3-Model3), 04 Feb (AA3-Model1), 10 Feb (AA4-Model1) The query
;WITH CTE1 AS(
SELECT
RnConcat = Model +'-Model' + CONVERT(varchar(10), ROW_NUMBER()
OVER(PARTITION BY ManfDate ORDER BY (SELECT 1)))
,ManfDate
FROM T),
CTE2 AS(
SELECT DISTINCT
ManfDate
,Model = STUFF((SELECT ','+ RnConcat
FROM CTE1 c1
WHERE c1.ManfDate=c2.ManfDate
FOR XML PATH('')),1,1,'')
FROM CTE1 c2),
CTE3 AS(
SELECT
ConcatedData = CONVERT(VARCHAR(7),ManfDate,6) + '(' + model + ')'
FROM CTE2)
SELECT DISTINCT
STUFF(( SELECT ', ' + ConcatedData
FROM CTE3
FOR XML PATH(''),TYPE)
.value('.','NVARCHAR(MAX)'),1,2,'') AS SingleRow
FROM CTE3