0

I have a table Orders, it has the following columns: 

OrderID, ClientID, BankNumber, Adres, Name;

I want to write a query that gives me this result: distinct clientid name and adres on one row with all the belonging orders and corespondating bankaccount numbers on one row: This is my example.

ClientID    Adres   Name    order1  Banknumber  Order2  Banknumber  order3  Banknumber
SQL_M
  • 2,455
  • 2
  • 16
  • 30
JohnDoe
  • 11
  • 2

2 Answers2

1

First you cannot query something and come up with a results set with infinite number of columns, but you could combine orders and show them in 1 column.

if you are on SQL Azure or SQL2017 you can also use STRING_AGG like this:

select customer.Id, customer.Name, orderSummary.orderData
(select  STRING_AGG(orderID+'-'+banknumber+', ') as orderData from orders where customerId = customer.Id)  orderSummary
from Customers as  customer

You can look at this post for more answers How to concatenate text from multiple rows into a single text string in SQL server? And Subquery from Microsoft: https://technet.microsoft.com/en-us/library/ms189575(v=sql.105).aspx

Ashkan S
  • 10,464
  • 6
  • 51
  • 80
0

here is a working sample. Hope it works for you.

You need to build the Order1, order2, order3...and BankNumber1,BankNumber2...dynamically. I have hard coded in my example

drop table #t1
create table #t1(OrderID Int, ClientID int, BankNumber varchar(50), [Address] varchar(50), Name varchar(50))

insert into #t1
select 1,11,'111','xyz1','xyz'
union all
select 2,22,'112','xyz2','xyzz'
union all
select 3,33,'113','xyz3','xyzzz'
union all
select 100,11,'111','xyz1','xyz'
union all
select 200,22,'112','xyz2','xyzz'
union all
select 300,33,'113','xyz3','xyzzz'

;with cte as 
(
    select OrderID,ClientID,BankNumber,Address,Name,ROW_NUMBER()over (partition by clientid order by orderid asc) RN
    from #t1
)
select ClientID
        ,max([Order 1]) Order1
        ,max([Order 2]) Order2
        ,max([BankNumber 1]) BankNumber1
        ,max([BankNumber 2]) BankNumber2
from 
(
    select ClientID,Address,Name,OrderID,BankNumber,'Order '+cast(rn as varchar(10)) OrderSeq
                                                    ,'BankNumber '+cast(rn as varchar(10)) BankNumberSeq
    from cte
) as ST
pivot(max(OrderID) for OrderSeq in ([Order 1],[Order 2])) as pt1 
pivot(max(BankNumber) for BankNumberSeq in ([BankNumber 1],[BankNumber 2])) as pt2
group by ClientID
SQLHelp
  • 41
  • 4