-3
ALTER PROCEDURE [dbo].[SpInvoiceSel]
AS
BEGIN
      Declare @json varchar(MAX) 

    SET @json = (
            select (
                select InvoiceId as 'invoiceId',
                       i.CustomerId as 'customerId',
                       o.OrganizationName as 'organizationName',
                      (p.FirstName +  p.LastName) as 'fullName',
                       sum(i.TotalRate) as 'totalrate'
                       from Invoice as i
                       inner join  Customer as c
                       on c.CustomerId = i.CustomerId
                       inner join Organization as o
                       on o.OrganizationId = c.OrganizationId
                       inner join Employee as e
                       on e.EmployeeId = i.EmployeeId
                       inner join Person as p
                       on p.PersonId = e.PersonId
                    
                     group by  InvoiceId,i.CustomerId,i.EmployeeId,p.FirstName,p.LastName,i.TotalRate,o.OrganizationName
            FOR JSON PATH, include_null_values
            ) as 'data' for json path, without_array_wrapper
    )
    select @Json as 'Json'
                  
END

enter image description here

"Nathan field and Robert Plant are in different column" Now i need both of them in a single row

Thom A
  • 88,727
  • 11
  • 45
  • 75
cod zard
  • 1
  • 1
    Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. [ask] – philipxy Oct 12 '20 at 08:26
  • 1
    Why are you aggregating on `i.TotalRate` *and* grouping on it? – Thom A Oct 12 '20 at 08:29
  • Possible duplicate of [How to return multiple values in one column (T-SQL)?](https://stackoverflow.com/q/122942/3404097) – philipxy Oct 12 '20 at 08:33
  • 1
    *"Nathan field and Robert Plant are in different column"* no that aren't, they're both in the same column; the column `Employee`. Expected results will help us help you here. – Thom A Oct 12 '20 at 08:53
  • Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code in table format. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. – philipxy Oct 12 '20 at 08:55
  • This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See [ask] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. – philipxy Oct 12 '20 at 08:56

1 Answers1

1

use CONCAT() function

CONCAT (InvoiceId , i.CustomerId , o.OrganizationName, p.FirstName, p.LastName ) as Mycolumn

You got the idea

t-sql: https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql?view=sql-server-ver15

Augustus
  • 383
  • 3
  • 13
  • 2
    The question is about tsql. – philipxy Oct 12 '20 at 08:32
  • t-sql should also have Concat function https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql?view=sql-server-ver15 – Augustus Oct 12 '20 at 08:42
  • It does, @Augustus, the point is that linking to MySQL Documentation for a T-SQL based question isn't relevant. Same name function can behave (very) differently on different RDBMS. – Thom A Oct 12 '20 at 08:51