How to Get Row wise Query [Resolved]

Posted by Jayakumars under ADO.NET on 7/27/2015 | Points: 10 | Views : 373 | Status : [Member] [MVP] | Replies : 1
Hi

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


Create Table TblMachineDetails
(
Model varchar(20),
ManfDate date,
Diaset varchar(20)
)

Insert into TblMachineDetails values('AA1','02/02/1981','AB1')
Insert into TblMachineDetails values('AA2','02/02/1981','AB2')
Insert into TblMachineDetails values('AA3','04/02/1981','AB3')
Insert into TblMachineDetails values('AA3','04/02/1981','AB4')
Insert into TblMachineDetails values('AA4','10/02/1981','AB5')


Select * from TblMachineDetails



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

Mark as Answer if its helpful to you


Responses

Posted by: Rajnilari2015 on: 9/27/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
1
Down

Resolved
Here is my attempt

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


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

*/

Hope this helps

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response