0

In my database there is a table with values below

image attachedenter image description here

In need a qry to get the result like this

enter image description here

Shyam Prasad
  • 974
  • 1
  • 6
  • 17
  • use stuff in your query – Sandip - Frontend Developer Feb 08 '18 at 10:44
  • @SandipPatel STUFF doesn't perform string aggregation – Panagiotis Kanavos Feb 08 '18 at 10:51
  • The artible [Grouped Concatenation in SQL Server](https://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation) describes and compares all of the available methods to concatenate strings. This question has been asked many times before and all answers use it as a reference – Panagiotis Kanavos Feb 08 '18 at 11:01
  • The accepted answer in the linked question has one flaw actually (read the comments). It will not re-escape xml entities (in case your string includes characters such as `<, > or &`. The solution is `TYPE` in connection with `.value()`. So the here given answer by Sandip Patel seems to be a good fit... – Shnugo Feb 08 '18 at 11:03
  • @PanagiotisKanavos, You are right, that quesitons should not be answered again and again. But: In many cases older questions do not show the best / up-to-date approach. I've closed this as duplicate, then tool a closer look at the accepted answer in the linked question. Just closing duplicate questions can lead to sub-optimal answers.... – Shnugo Feb 08 '18 at 11:05
  • @Panagiotis: look at your answer : https://stackoverflow.com/a/48620661/6606630 (this also answerd so many time, even you answer), can i know why? – Sandip - Frontend Developer Feb 08 '18 at 11:07
  • @SandipPatel because you posted the *wrong* explanation and code you found somewhere else without attribution or even understanding what it does. Because such things make it *harder* to find answers. Because in the end it may be faster to post a quick answer than search for the duplicate. – Panagiotis Kanavos Feb 08 '18 at 11:09
  • I already told you I have write code own. nice thing your code naturally and mine was copy paste: good mate nice thinking.... – Sandip - Frontend Developer Feb 08 '18 at 11:10

2 Answers2

1

Use XML query as below;

DECLARE @tblQuestion AS Table
(
    SID INT,
    Value VARCHAR(50)
)

INSERT INTO @tblQuestion VALUES(1,'stu')
INSERT INTO @tblQuestion VALUES(1,'vtu')
INSERT INTO @tblQuestion VALUES(1,'ztu')
INSERT INTO @tblQuestion VALUES(2,'stu')
INSERT INTO @tblQuestion VALUES(2,'vtu')

select distinct t.SID,
  STUFF((SELECT distinct ', ' + t1.Value
         from @tblQuestion t1
         where t.SID = t1.SID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') Value
from @tblQuestion t;

Output:

enter image description here

Sandip - Frontend Developer
  • 14,397
  • 4
  • 35
  • 61
1
DECLARE @TAB TABLE(SIDS INT,VALUE VARCHAR(10))

INSERT INTO @TAB

SELECT 1,'ASC'
UNION ALL
SELECT 1,'ASC'
UNION ALL
SELECT 1,'ASC'
UNION ALL
SELECT 2,'SDF'
UNION ALL
SELECT 2,'SFD'
UNION ALL
SELECT 3,'ERF'
UNION ALL
SELECT 3,'ERF1'


SELECT T1.SIDS,VALUE = STUFF((SELECT ','+T2.VALUE FROM @TAB T2 WHERE T1.SIDS = T2.SIDS FOR XML PATH('')),1,1,'')
FROM @TAB T1
GROUP BY T1.SIDS

OUTPUT

SIDS    VALUE
1   ASC,ASC,ASC
2   SDF,SFD
3   ERF,ERF1
Ajay
  • 764
  • 4
  • 12
  • Same as the other answer, no explanation, the same thing has been answered with full explanations dozens of times. Adding *another* answer, especially without explanation, doesn't help anyone. It actually makes it HARDER for the next person to find the solution – Panagiotis Kanavos Feb 08 '18 at 11:04
  • hmmm.....may be :) – Ajay Feb 08 '18 at 11:07