Query group by data in SQL Server - cursor?

aakshi jain
aakshi jain used Ask the Experts™
on
Hello Experts,
Need this.
Data(changed data)
Keycol data
1      O1
1      O2
1      O3
2      O4
3      O5
3      O5
7      O2
7      O1
Output I need below

Keycol data       LineNo
1      O1,O2,O3    1
2      O4          2
3      O5          3
7      O1,O2       4
possible in sql query? I need to generate  sequential number for a new column.
DO i need a loop or cursor forthis?
Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
Loop is not required for this. Let me just write a query for you.
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Hi,
Please try this-

--

CREATE TABLE Teststr
(
	Keycol INT , data VARCHAR(100)
)
GO

INSERT INTO Teststr VALUES
(1      ,'O1'),
(1      ,'O2'),
(1      ,'O3'),
(2      ,'O4'),
(3      ,'O5'),
(3      ,'O5'),
(7      ,'O2'),
(7      ,'O1')
GO

SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) [LineNo] FROM 
(
	SELECT DISTINCT Keycol  , STUFF 
					((
					SELECT DISTINCT ', ' +  data
					FROM Teststr b
					WHERE ( a.Keycol = b.Keycol )				
					FOR XML PATH('')
					) ,1,2,'') 
					AS Data 
	FROM Teststr a
)k

--

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
Keycol      Data                                                                                                                                                                                                                                                             LineNo
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------
1           O1, O2, O3                                                                                                                                                                                                                                                       1
2           O4                                                                                                                                                                                                                                                               2
3           O5                                                                                                                                                                                                                                                               3
7           O1, O2                                                                                                                                                                                                                                                           4

(4 row(s) affected)

Open in new window


Hope it helps!

Author

Commented:
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial