How to Get Row wise Query [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

1	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

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

Latest Posts