0

I have data as follows: We call this table Client_benefits

Client_name, Income_type, Frequency
Joe Smith SSI Month
Joe Smith Work Week

I would like an out put row as follows: I would like something like

Select Client_name, Income_type, Frequency from Client_benefits

Do I use grouo_by?

To create the singular per client output I need?

Joe Smith SSI Month Work Week

Rather than currently I am getting duplicates but would like to see one row if possible.

Voice Sober
  • 1
  • have you tried using distinct? – Exelian Dec 22 '20 at 18:59
  • What's the exact expected result according to your sample data? – The Impaler Dec 22 '20 at 19:01
  • Joe Smith SSI Month Work Week evreything on one row – Voice Sober Dec 22 '20 at 19:12
  • Does this answer your question? [SQL Query to concatenate column values from multiple rows in Oracle](https://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle) – JonathanDavidArndt Dec 22 '20 at 19:15
  • Maybe but it's Oracle. – Voice Sober Dec 22 '20 at 19:24
  • 1
    Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Dec 22 '20 at 23:35

2 Answers2

0

I've seen this before. You want output similar to:

Joe Smith "SSI Month" "Work Week"

It is doubtful that you will be able to segregate these into their own columns, but you can concatenate them into a single field.

That question has already been asked and answered here:

SQL Query to concatenate column values from multiple rows in Oracle

JonathanDavidArndt
  • 2,518
  • 13
  • 37
  • 49
  • that's an Oracle syntax so I am not sure about this. Usually there is syntax differences to the extent you cannot rely on using Oracle code in a SQL Server. – Voice Sober Dec 22 '20 at 19:25
0

You can write the query as:

 SELECT Client_name+ ' ' +  
    STUFF((SELECT ', ' + Income_type + ' ' + Frequency
           FROM Client_benefits CBIn 
           WHERE CBIn.Client_name = CBOut.Client_name 
          FOR XML PATH('')), 1, 2, '')
FROM Client_benefits CBOut
GROUP BY Client_name

SQL Demo here..

Deepshikha
  • 9,896
  • 2
  • 21
  • 21