How to select single row in multiple records in sql server based on date

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 519
Suppose we have

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


And we need

SingleRow 
02 Feb (AA1-Model1,AA2-Model2,AA3-Model3), 04 Feb (AA3-Model1), 10 Feb (AA4-Model1)


We can use the below query

USE tempdb

--Create a table
CREATE TABLE TblMachineDetails
(
Model VARCHAR(20),
ManfDate DATE,
Diaset VARCHAR(20)
)
--Insert the records
INSERT INTO TblMachineDetails 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')

--Query Start
;WITH CTE1 AS(
SELECT
RnConcat = Model +'-Model' + CONVERT(varchar(10), ROW_NUMBER()
OVER(PARTITION BY ManfDate ORDER BY (SELECT 1)))
,ManfDate
FROM TblMachineDetails),
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
--End Query

DROP TABLE TblMachineDetails

Comments or Responses

Login to post response