0

I have a temp table #TestData as below where data is inserted using sub-query

Id      Amount         Ccy    Description
2012    2373350.000    INR    test1
2012    2321030.000    INR    test12
2014    2350.000       INR    test4
2014    30.000         USD    test5
2014    130.000        USD    test6

code updated : using stuff and group by to concatenate strings hoever getting error invalid object Tmpta

      INSERT  INTO #TestData

       -- SELECT  Tmpta.Id,Tmpta.Amount,Tmpta.Ccy,tmpta.Description
     SELECT  Tmpta.Id ,
                    STUFF(( SELECT  ',' + CAST(B.[Amount] AS VARCHAR(20))
                            FROM    Tmpta B
                            WHERE   B.id = Tmpta.id
                            ORDER BY B.[Amount]
                          FOR
                            XML PATH('')
                          ), 1, 1, '') AS Amount ,
                    STUFF(( SELECT  ',' + B.[cur]
                            FROM    Tmpta B
                            WHERE   B.id = Tmpta.id
                            ORDER BY B.[cur]
                          FOR
                            XML PATH('')
                          ), 1, 1, '') AS Ccy ,
                    STUFF(( SELECT  ',' + B.[Description]
                            FROM    Tmpta B
                            WHERE   B.id = Tmpta.id
                            ORDER BY B.[Description]
                          FOR
                            XML PATH('')
                          ), 1, 1, '') AS Description
         FROM    ( SELECT SubQuery
                  ) Tmpta              

         WHERE   tmpta.Id in (2012,2014)
         group by Tmpta.id

I want to merge data as below : 

Id      Amount                         Ccy          Description
2012    2373350.000,2321030.000        INR,INR      test1,test12
2014    2350.000,30.000,130.000        INR,USD,USD  test4,test5,test6

any idea how we can achieve this?

Please help. Thanks!

Harshit
  • 149
  • 3
  • 14
  • Upgrade your version of SQL Server and use `string_agg()`. – Gordon Linoff Feb 14 '20 at 11:35
  • 3
    Using `STUFF` and `FOR XML PATH`. There's plenty of examples of how to generate a delimited list in SQL Server. [site:stackoverflow.com create comma delimited list SQL server](https://www.google.com/search?q=site%3Astackoverflow.com+create+comma+delimited+list+SQL+server) – Thom A Feb 14 '20 at 11:39
  • Personally, however, I would suggest avoiding this type of data. This makes it so that things like ordinal position has meaning, and functions like `STRING_SPLIT` don't support that. Stick to keeping your data normalised. – Thom A Feb 14 '20 at 11:52
  • Does this answer your question? [How to use GROUP BY to concatenate strings in SQL Server?](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) – Marc Guillot Feb 14 '20 at 13:50
  • @MarcGuillot . No i have edited the question. – Harshit Feb 17 '20 at 06:16

2 Answers2

2

if you want to use subquery then you can to put that data into the temp table and use that table in the query except using a subquery

DROP TABLE #Tmpta

SELECT ID,Amount,cur,Description INTO #Tmpta from data A

SELECT #Tmpta.Id , STUFF(( SELECT ',' + CAST(B.[Amount] AS VARCHAR(20)) FROM #Tmpta B WHERE B.id = #Tmpta.id ORDER BY B.[Amount] FOR XML PATH('') ), 1, 1, '') AS Amount , STUFF(( SELECT ',' + B.[cur] FROM #Tmpta B WHERE B.id = #Tmpta.id ORDER BY B.[cur] FOR XML PATH('') ), 1, 1, '') AS Ccy , STUFF(( SELECT ',' + B.[Description] FROM #Tmpta B WHERE B.id = #Tmpta.id ORDER BY B.[Description] FOR XML PATH('') ), 1, 1, '') AS Description FROM #Tmpta 

     WHERE   #Tmpta.Id in (2012,2014)
     group by #Tmpta.id
Swapnil Mhaske
  • 141
  • 1
  • 6
0

try below query

    SELECT
    ID,  string_agg(Amount,',') As Amount, string_agg(ccy,',') as CCY, string_agg(description, ',')  
FROM
    <table name>
GROUP BY
    ID;
Thom A
  • 88,727
  • 11
  • 45
  • 75
Laxmikant
  • 588
  • 4
  • 11