How to loop through a duplicate row and concatenate value column

Asked Mar 21 '21 at 18:14

Active Mar 27 '21 at 09:24

Viewed 156 times

I have a sql query which i managed to filter joining different tables, Below

NoOfCounts - Duplicate count of same Question in a Form having different Answer
NoOfRowsPerAnserId - Used a Rank Partition by and order by AnswerId

My resultset looks somewhat like this now,

What I expect ( I just omitted other column but it can be there if this is not possible)

Query I have

CREATE Table #myData ( Id INT Identity (1,1) ,NoOfCounts INT ,NoofRowsPerAnswerId_RankFunction INT, AnswerId INT, Value NVARCHAR(256) , FormId INT, QuestionId INT, AnserNameofItem NVARCHAR(256), DesiredValue NVARCHAR(256))

INSERT INTO #myData 
VALUES
(1,1,11,90,101,1,'Equity','') 
,(2,1,22,$1000,101,2,'CashInFlow','')
,(2,2,22,$1001,101,2,'CashOutflow','')
,(3,1,33,$99,101,3,'Chase','')
,(3,2,33,$100,101,3,'Capital','')
,(3,3,33,$101,101,3,'Citi','')
,(4,1,44,$800,101,4,'Equity','')
,(4,2,44,$801,101,4,'Fund','')
,(4,3,44,$802,101,4,'Credit','')
,(4,4,44,$804,101,4,'Debit','')


SELECT * FROM #myData

edited Mar 21 '21 at 18:44

asked Mar 21 '21 at 18:14

user3920526

The answer will depend on your version. 2017+ has string_agg(). <2017 ,,, the stuff xml approach – John Cappelletti Mar 21 '21 at 18:21
I think we have 2016 in Prod. – user3920526 Mar 21 '21 at 18:23
Should not have taken the suggestion . Can anyone help me with this . Thank you – user3920526 Mar 21 '21 at 19:03
Take a peek at https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=d568e478dc55fdb982c3a8b15a9603b6 (everyone needs a little nudge from time-to-time) – John Cappelletti Mar 21 '21 at 19:16
Thank you very much John , can you please put that as an answer. I will confirm it . – user3920526 Mar 21 '21 at 19:50
I have learnt a new way to loop . Without using While loop – user3920526 Mar 21 '21 at 19:57
why do you have these two as MIN and MAX , i did not get this part ID =min(ID) ,AnswerID =max(AnswerID) – user3920526 Mar 21 '21 at 20:09
gotcha need a aggregate function for a group by clause – user3920526 Mar 21 '21 at 20:11

How to loop through a duplicate row and concatenate value column

0 Answers0